Aggregate Functions in header of subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform within my main form. The main form shows the project
information (single view) and the subform shows the project details
(continuous view).

I used an SQL Aggregate function in the header of my subform to display the
sum of the project details. I also used a Domain Aggregate function in the
same subform header to display the sum of the project details for all
projects for that particular client. I also added a couple of text boxes to
say "Total for " & [Client Name] and "Total for " & [Project Name].

Now, everything works great when there are actually project details to
display. When there are no project details for a particular project (as
would be the case when the user first starts entering information), my
aggregate functions don't display zero - they display nothing. My text boxes
also display nothing. It seems as though when there is no data to be
displayed in details, none of the text box controls are calculated.

Why does this occur?
 
It's just the way that it is. DSum (for example) will return a null value
if there are no records for it to sum. Take a look at the Nz function to
convert the null values to 0 (or whatever) for display purposes.
 
2 Questions:

1) Why do my text boxes with references to text fields outside of the
subform also return null values?

2) My top level DSum calculates based on all records for the client, not
just those for the specific project being looked at in the subform. So, it
should have values to calculate. For example, when there are records in the
subform, the client level DSum may show $100, and the project level DSum may
show $25. When there are no records in the suform, both the client level
DSum and the project level DSum are empty.

Thanks for your help.

Rob Oldfield said:
It's just the way that it is. DSum (for example) will return a null value
if there are no records for it to sum. Take a look at the Nz function to
convert the null values to 0 (or whatever) for display purposes.


KellyB said:
I have a subform within my main form. The main form shows the project
information (single view) and the subform shows the project details
(continuous view).

I used an SQL Aggregate function in the header of my subform to display the
sum of the project details. I also used a Domain Aggregate function in the
same subform header to display the sum of the project details for all
projects for that particular client. I also added a couple of text boxes to
say "Total for " & [Client Name] and "Total for " & [Project Name].

Now, everything works great when there are actually project details to
display. When there are no project details for a particular project (as
would be the case when the user first starts entering information), my
aggregate functions don't display zero - they display nothing. My text boxes
also display nothing. It seems as though when there is no data to be
displayed in details, none of the text box controls are calculated.

Why does this occur?
Click to expand...
Click to expand...
 
I'd say that it's something to do with the location of the text fields, the
source of those text fields and the order that they're being calculated.
Could you give a bit more detail of the source for the text boxes, and where
it they are i.e. are they on the sub form or the main form?


KellB said:
2 Questions:

1) Why do my text boxes with references to text fields outside of the
subform also return null values?

2) My top level DSum calculates based on all records for the client, not
just those for the specific project being looked at in the subform. So, it
should have values to calculate. For example, when there are records in the
subform, the client level DSum may show $100, and the project level DSum may
show $25. When there are no records in the suform, both the client level
DSum and the project level DSum are empty.

Thanks for your help.

Rob Oldfield said:
It's just the way that it is. DSum (for example) will return a null value
if there are no records for it to sum. Take a look at the Nz function to
convert the null values to 0 (or whatever) for display purposes.


KellyB said:
I have a subform within my main form. The main form shows the project
information (single view) and the subform shows the project details
(continuous view).

I used an SQL Aggregate function in the header of my subform to
Click to expand...
display
the
sum of the project details. I also used a Domain Aggregate function
Click to expand...
in
the
same subform header to display the sum of the project details for all
projects for that particular client. I also added a couple of text
Click to expand...
boxes
to
say "Total for " & [Client Name] and "Total for " & [Project Name].

Now, everything works great when there are actually project details to
display. When there are no project details for a particular project (as
would be the case when the user first starts entering information), my
aggregate functions don't display zero - they display nothing. My
Click to expand...
text
boxes
also display nothing. It seems as though when there is no data to be
displayed in details, none of the text box controls are calculated.

Why does this occur?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top