Zero Value (1 Viewer)

[shawnacker]

Hi! I have a query which counts the number of certain occurences in a particular month. In one month there were no occurences. Is there a way to have my query results show a 0 instead of nothing at all?

Thanks in advance,

Shawn

 

[pcs]

in general, use the Nz Function...

in your query use the expression builder to create an expression like:

Nz([yourfield],0) and do your count on the expression.

hth,
al

 

[shawnacker]

I tried using the NZ function and my query still returned nothing. Here is a more detailed explanation of my problem.

I have a DatePart function which allows the user to enter the month and year that they are looking for in my table. My table has a date column, and a Reason code (which is basically a lookup from another table).

So my table currently looks as follows:

REASON DATE
T-06 6/29/01
T-06 6/30/01

In my query I originally had: Title: Count(*) with expression in the Total column.

This worked fine with the month of June (result of 2) but produced no result for the month of May (which I want as 0). I have tried variations of the NZ for expression and count but still came up with no result.

Any help would be greatly appreciated.

Thanks,

Shawn

 

[pcs]

where did you try the Nz...

try nz([date,0]) as an expression in the date...and nz([reason],0) in the reason.


hth,
al

oops, don't think that's gonna work...checking...



[This message has been edited by pcs (edited 08-07-2001).]

 

[shawnacker]

I created a column which displays the count number. I have it counting the number of entries for the month. I also tried it with counting the reason codes and it (obviously) gave me the same number. Your newest suggestion produced the same result with no amount displayed for the month of May.

-Shawn

 

[pcs]

shawn,

your query is obviously returning an empty recordset, so nz won't work.

you mentioned that the reason code comes from another table, could you provide more info...

(you may have to deal with the problem in the form/report that the query feeds)

al




[This message has been edited by pcs (edited 08-07-2001).]

 

[Rich]

Access doesn't know that you wish to return a value for data that doesn't exist, you will either have to create a function, use 12 Iif statements, or create a lookup table with all the Months listed and then do a comparison in the query.
HTH

 

[port]

shawnacker,
i m having the same problem with count = 0. Have you had any success

port