By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Problems Using Count Queries

P: 9
I've run into a little problem with a form I'm trying to make, and I think it has to with my query design. Here's what I'm trying to do:

I have query 1, query 2 and query 3. Query one counts all the rows with a "1" in column A, query 2 counts all the rows with a "2" in column A and query 3 counts all the rows with a "3" in column A.

Basically, the problem happens if any of these counts happens to be 0. Instead of a 0 coming up as the result of the query, the results will just be blank.

This normally wouldn't be a problem, but when I try to include these query results in another query or a form, it makes the whole query or form display nothing, not just the query that should be blank.

Any thoughts?

Edit: I'm using Access 2007
Feb 2 '09 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,287
You can add a column to your query and set it to Expression. Use

nz(Count([column A]),0)

That will give you a zero even if there are no matching records.
Feb 2 '09 #2

P: 9
Not quite getting it. Right now I have 2 columns in my query.

Column 1 has:
Table: "Master"
Field: "ID"
Total: "Count"
And this column is showing

Column 2 has:
Table: "Master"
Field: "Source"
Total: "Group by"
Criteria: "1"
And this column is not showing.

And the problem obviously comes when there are no values of "1" inside of the column named source.

Are you saying I should ass in another column? What would I put in for the table and field? And where would I put in the expression you suggested? In criteria?
Feb 2 '09 #3

Expert 100+
P: 1,287
Right, add another column. Set
Total: "Expression"
Field: "WhatEverNameYouWant: nz(count(ID),0)"
Table is blank.
Feb 2 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.