place "0" in blank field

kbreiss

Registered User.
Local time
Today, 15:55
Joined
Oct 1, 2002
Messages
228
place "0" in blank field

I'm trying to place a "0" in the count field of this query when the count field for a particular number is blank. Is this possible? Here's my query that gives me the correct results, but when a number has no counts it is BLANK instead having a "0" like I would like it.

SELECT DISTINCT tblNumbers.NUMBERS, qry1stNumbers.COUNT1
FROM tblNumbers LEFT JOIN qry1stNumbers ON tblNumbers.NUMBERS = qry1stNumbers.FIRST_BALL
GROUP BY tblNumbers.NUMBERS, qry1stNumbers.FIRST_BALL, qry1stNumbers.COUNT1;

Any help would be greatly appreciated.

Thanks,
Kacy
________
Buy Marijuana Seeds
 
Last edited:
If used in SQL: NVL(«Your field», «Value If Blank»)
If used in VBA: NZ(«Your field», «Value If Blank»)
 
Can I put the Nz function into a query? I tried putting Nz before my counts field, but still leaving the field blank if there is no data. In the help section it doesn't give any examples of this use in a query. Just checking to see if I can do this function in a query.

Thanks,
Kacy
________
Dodge Hemi Small Block Specifications
 
Last edited:
When using the NVL i'm getting an "undefined function in expression" error.

Here's the query.....
SELECT DISTINCT tblNumbers.NUMBERS,
NVL(qry1stNumbers.COUNT1,0)
FROM tblNumbers LEFT JOIN qry1stNumbers ON tblNumbers.NUMBERS = qry1stNumbers.FIRST_BALL
GROUP BY tblNumbers.NUMBERS, qry1stNumbers.FIRST_BALL, qry1stNumbers.COUNT1;
________
Hemp Marijuana
 
Last edited:
I've got it working........

SELECT DISTINCT tblNumbers.NUMBERS, NZ(qry1stNumbers.COUNT1,[0])
FROM tblNumbers LEFT JOIN qry1stNumbers ON tblNumbers.NUMBERS = qry1stNumbers.FIRST_BALL
GROUP BY tblNumbers.NUMBERS, qry1stNumbers.FIRST_BALL, qry1stNumbers.COUNT1;
________
N02 Vaporizer
 
Last edited:

Users who are viewing this thread

Back
Top Bottom