Counting Records In Query

[paulmcdonnell]

I need a query to return a 0 and not just cave in?

Hi guys

I have a query A which collates the counted results of other queries B1 B2 B3.

I.e three separate queries count the number of records in 3 different recordsets. query A is a simple query collating these results in one record set.

Should any of the count queries (B1 B2 or B3) return no items then that query is empty and my summation query A returns a blank also (even if other queries B2 and B3 do report a count).

If your following this ... I need to know how to get a query to report 0 if there are no records to count.


Anyone have any ideas..

THANKS
PAUL

 

[bakerboy_1111]

Hi Paul,

I think I know a way but it may not be the most efficient though.

For the queries, B1, B2 and B3, you could perhaps add an expression that does the following: (Assume the answer for your count is is a column called Count)

Expr1: Iif(IsNull([Count]), 0, [Count])

I think this code is right but I'm not sure, try anyway.

As I don't know your code for the count that you did I had to create an extra column that is based on your answer, but you could also enter your code in my code wherever I've written [Count], and hopefully this will run your count, and return zero if there is no result.

Hope I've explained ok. (Also hope it works!!!)

Bakerboy