Access ACCESS 2002 Expressions Issues

[tom.still]

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

 

[Ian]

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

 

[tom.still]

I I will have a look thanks

 

[tom.still]

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?

 

[Temmu]

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.

 

[tom.still]

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

 

[Temmu]

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".)

 

[rachelm920]

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.