Avoiding Empty Records

[jkfeagle]

This is likely a no-brainer but for some reason I'm hitting a wall on this one today and don't have a whole bunch of time to figure it out. I'm running a query that sums a large set of financial data. If all account numbers are listed in the report, the report comes to about 750 pages. The thing is, a good number of the account numbers have no data for all 12 months. What I need my query to do is somehow evaluate the entire record and if all 12 months for an account are empty, disregard the record. Doing one month is simple enough but how do I make it evaluate all 12 months?? If even one month has data it needs to be included. HELP!!

 

[JMG]

In your report, the field that has no data, [ =NZ([yourfieldname],0) ]
no brackets of course

 

[jkfeagle]

I would really prefer to head this off at the query level because the Account number will show regardless of data assigned to it. What I'm really looking for is maybe a criteria to put in the query where I can link all the month fields and if all are null then the record is ignored.

 

[JMG]

You can use the same thing in a query. If you do not use the nz() function, you won't be able to do any math on items that have even one null month, ie. if jan, feb, mar have data and apr does not, you cannot add jan+feb+mar+apr. you will get a null answer (if you even get an answer). Access will ignore the null answer.

Hope I am helping and not confusing you.

edit: it might help you to look up nz() in help