How to grand total expression value fields in query

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Please help!!

I have created a selection query which selected all records within a date
range, some of the fields are: food bags provided, clothing vouchers
provided, gift certificate value provided, utilitiy value provided, rent
value provided.

My second query sums all of the above fields and created new fields for the
food and clothing value (i.e. sum food bags * $30 and sum clothing vouchers
provided * $25). The result of this query is one record with alll of the
totals.

I now want to total all of the value totals (food value, clothing value,
gift certificate value, utility value, rent value). I have tried adding a
field in query 2, calling it total value and adding all of the fields
together. I don't get an error and the field shows in the query result but
it is empty.

I then tried creating query 3, included all of the fields needed for my
calculation, created a new field and added all of the value fields. Same
result as above, no error, field shows but is empty.

I then tried doing the grand total calculation on my report, in all
sections. I put the calculation in the control source for the text box that
I used but now the report asks me for a query 2 parameter(no idea what it is
asking for or why). The only criteria I used was in query 1 for the date
range. Everything else is simple expressions.

What am I doing wrong? Is it possibly in my syntax, what should it be? I
know this is probably something really easy but I'm getting frustrated.

Please help.
Thank You.
 
Hi Karen

Are any of your totals NULL. If so adding all of them together would give
you NULL and a blank field.
There won't be an error because the result will be correct, though not what
you wanted or expected.

Try using the Null to Zero function on each like: NZ(FoodValue) +
NZ(ClothingValue) etc.
 
Back
Top