Summing query values with blanks

[ebarrera]

I tried to search to see if this was brought up before, but don't know what this is called. Sorry if this was already solved and hope you can give me the keyword to search.

I am working on a query that counts the number of ethnicity responses for each active job posting. We have a online recruitment process where we are given datafilles containing applicant information. We need reports so, I am creating a database that translates the data into a report.

One query was setup to give me all the reponses for active recruitments (Active - EEO Responses). I then seperated them into 6 seperate queries to individualize the ethnicities (Active - Ethnity (Hispanic). I then created 6 seperate queries each giving me counts on those ethinicity queries (i.e. Hispanic counts, White counts). Finally, I created a query that merges the 6 count queries into one. I set it to give me all records from EEO Response and got a query with blanks a few blanks because a recruitment may not have a applicant that a particluar group.

Now, I have to do some calculations on this query but a cannot because the blanks are not values. How can I make the blanks into values (default to zero), so that I can do a simple addition formula. I have attached a screen shot of what I am up against.

NOTE: I am not an advance programmer, but catch on really quick, so any help is appreciated.

 

[pbaldy]

Generally you use the Nz() function:

Nz(FieldName, 0)

 

[georgedwilkinson]

In Access, surround the field names with the Nz function, like this:

Nz(MyFieldName,0)

Using any other SQL, use the Coalesce function:
Coalesce(MyFieldName,0)

 

[ebarrera]

First question asked and problem solved. Thanks everyone, the Nz() works. I hope all my questions are this easy.

 

[pbaldy]

Happy to help. Welcome to the site by the way.

 

[ebarrera]

Sorry, but Exp1: NZ(FieldName,0) is not giving me numeric values, which is a problem when adding Total: Exp1 + Exp2 to get a total. How do I convert the value to a numeric zero?

 

[pbaldy]

That would imply that the data in FieldName is not numeric, as that should return a numeric value. Is it? You can use a conversion function to force the issue. CCur(), CInt(), CDbl(), etc.

 

[ebarrera]

Aha! The conversion worked, I now have a total count...Thanks everyone.

 

[georgedwilkinson]

Excellent! Good luck!