nulls

[Brenda @ AIM]

In a select query how can you get zeros to replace nulls for calculating.
When I put the following statement in the Critieria box I get an error
message.
'COUNT([KITMAST].[COUNT])=Nz([COUNT],0)'
It tells me I have a type mismatch. The field is a number field set as an
integer.

 

[Jeff Boyce]

Brenda

If your query field is 'Nz([yourfield],0)', and your criterion under that is
'=0', I think you'll get all the records with Null or 0 from the query ...
then count those.

By the way, "Count" is a reserved word in Access -- using it as a fieldname
could confuse both you and Access. Maybe you could change the table
fieldname to CountOfXXXXX or something?

 

[Brenda @ AIM]

I now have two problems. In the select query I also have a date range that
must be satisfied. When I go to my report to open it, it asks for a
parameter. Is this from the dates?
If I use the Nz function on my crosstab query it still doesn't put the zeros
in the null fields. I don't know the best way to handle this.
I need a summary of the kits.

 

[John Spencer (MVP)]

OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint

I now have two problems. In the select query I also have a date range that
must be satisfied. When I go to my report to open it, it asks for a
parameter. Is this from the dates?
If I use the Nz function on my crosstab query it still doesn't put the zeros
in the null fields. I don't know the best way to handle this.
I need a summary of the kits.

Brenda

If your query field is 'Nz([yourfield],0)', and your criterion under that is
'=0', I think you'll get all the records with Null or 0 from the query ...
then count those.

By the way, "Count" is a reserved word in Access -- using it as a fieldname
could confuse both you and Access. Maybe you could change the table
fieldname to CountOfXXXXX or something?

--
Good luck

Jeff Boyce
<Access MVP>

 

[Brenda @ AIM]

Here is the SQL code:
SELECT KITMAST.STATE, KITMAST.KIT, Count(Nz([KITMAST.COUNTOFKITS],0)) AS Expr1
FROM KITMAST
WHERE (((KITMAST.DATE_SHIP) Between #9/1/2003# And #4/30/2004#))
GROUP BY KITMAST.STATE, KITMAST.KIT
ORDER BY KITMAST.STATE;

I now have two problems. In the select query I also have a date range that
must be satisfied. When I go to my report to open it, it asks for a
parameter. Is this from the dates?
If I use the Nz function on my crosstab query it still doesn't put the zeros
in the null fields. I don't know the best way to handle this.
I need a summary of the kits.

Brenda

If your query field is 'Nz([yourfield],0)', and your criterion under that is
'=0', I think you'll get all the records with Null or 0 from the query ...
then count those.

By the way, "Count" is a reserved word in Access -- using it as a fieldname
could confuse both you and Access. Maybe you could change the table
fieldname to CountOfXXXXX or something?

--
Good luck

Jeff Boyce
<Access MVP>