Access ACCESS 2002 Expressions Issues

T

tom.still

Joined
Sep 12, 2007
Messages
4
Reaction score
0
Please HELP...

I have a query within Access 2002 that draws on 4 sums from 4 other queries. The 4 queries are essentially filters for 4 linked tables.

I am trying to create a total of these sums and am using this expression:

Total: Sum((Queryaccomsum!SumOfAmount)+(Queryothersum!SumOfAmount)+(Querypetrolsum!SumOfAmount)+(Querytravelsum!SumOfAmount))


PROBLEM: this expression works fine if there is a record in all 4 of the queries but if any one of them does not then the expression simply does not calculate. Is there any way I can get this field to calculate even if some of the queries are empty?

THANKS
 
I don't know much about this, but try looking up the NZ function in the help file. This is a Null Zero function and converts null (blank) values to zero. Or, you could set up an IIF statement and use IsNull.

Hope that helps :)
 
Thanks for your help Ian.

I can't make either of those suggestions work because it is not a null value that is creating the problem. It is not a missing value but a sum of a column of a table that has no records in it.

Does that make sense? Anyone?
 
perhaps add a row with 0's for the value column(s) and whatever fields are required to save the row.
methinks that will initialize the empty table.
 
Is there a way to make each new form generate a dummy row of 0's in the subforms so that the dummys have the corresponding form id
 
sure, "on open" and run a macro.
but why do that? once a table has a row, it should work, yes?
and... you don't want a bunch of empty rows in your table...

can you give a brief description of what each table does?

can you post the structure of your tables, perhaps by doing a screenshot of the relationships page?

(open the db, r click the white area, relationships.

ensure that all tables are showing by r clicking the relationships diagram and clicking "show all".)
 
Last edited:
I had the same problem with my access database summaries. Mine was for transactions completed by a certain user/date. Therefore I created a zero table in Access which each userid and date and added that into my summary. It makes it a lot easier. Just a suggestion.
 
Back
Top