Can't get NZ Function to Work

[Xiaoding]

Hi All,

I have a query with one field:
Count: DCount("TAG_NUMBER","H Pm's Closed, 1st TEMP").

It counts the number of records returned by the query "H Pm's Closed, 1st TEMP".

Occasionally, the "H Pm's Closed, 1st TEMP" query returns no records. So, I get no Dcount result. So the query shows no records returned. I need a zero there in that case.

I would like there to be a zero instead of no records, but I can't figure out how to do it. The NZ function is mentioned a lot, but I can't get it to work. Where do I use the NZ, or do I use something else, to get a zero instead of no records? I'm using Access 2003.

[maximus]

Try the Following:
1) Count: Nz(DCount("TAG_NUMBER","H Pm's Closed, 1st TEMP"),0)

2) Count: iif(isNull(DCount("TAG_NUMBER","H Pm's Closed, 1st TEMP")),0,DCount("TAG_NUMBER","H Pm's Closed, 1st TEMP"))


I have made adjustment to the second expression just check out whether it gives the error.

[Xiaoding]

Thnx for your reply,


The first one does not work, the second one returns the error, "The expression you entered has a function containing the wrong number of arguments".

Thank you

Xiaoding

[ConneXionLost]

Not saying this will fix the problem, but "Count" is a reserved word and should be avoided as a field name.

Cheers,

[Xiaoding]

Not saying this will fix the problem, but "Count" is a reserved word and should be avoided as a field name.

Cheers,

Thnx, I didn't know that! But, as you thought, that's not the problem.

[ConneXionLost]

Occasionally, the "H Pm's Closed, 1st TEMP" query returns no records. So, I get no Dcount result. So the query shows no records returned. I need a zero there in that case.

If your query "H Pm's Closed, 1st TEMP" is not returning a recordset (due to nulls) then, for that instance, there is NO field named "TAG_NUMBER". As a result, Access can't see any nulls for Nz to act on in the first place.

You may need to alter your "H Pm's Closed, 1st TEMP" query.

Nulls

Cheers,

[Xiaoding]

Well, that sounds logical. Unfortunetly, it adds up to the same question, since the NZ function is used, in queries, to deal with null results. So the question still is, how do I get NZ to work in this query? Or something else?