count returns null instead of zero :(

[SirStevie3]

Hey all, so i'm still new here and still new to Access so bear with me here... i learned by experimenting and google. i've pretty much used design view to build all my queries but i'm just beginning to learn SQL now.

I have a count column in this query, and i would like for it to return a zero instead of null if it doesnt find anything to count. not sure how much info you guys need, but here's the SQL for the query.

SELECT Documents.Status, Count(Documents.Document) AS CountOfDocument
FROM [Request Details] INNER JOIN Documents ON [Request Details].Request_ID = Documents.Request_ID
GROUP BY Documents.Status, [Request Details].Contract, [Request Details].CDRL, [Request Details].Change_Cycle
HAVING (((Documents.Status)="No Record") AND (([Request Details].Contract)=[Forms]![Report Runner]![Contract]) AND (([Request Details].CDRL)=[Forms]![Report Runner]![CDRL]) AND (([Request Details].Change_Cycle)=[Forms]![Report Runner]![ChangeCycle]));

any help is much appreciated, i've googled this topic to no end and havent found a solution that works. :banghead:

thanks so much.

 

[pr2-eugin]

SirStevie3, did you try wrapping them in Nz function? Like..

SELECT Documents.Status, [B]Nz(Count(Documents.Document), 0) AS CountOfDocument[/B]

 

[SirStevie3]

yes i did as a matter of fact. that seemed to be the most popular result when googling this issue, but it doesnt change anything for me. i just get my column headers with no records just like always.

Also, i'm using Access 2003.... if that makes a difference.

thanks for the reply!

 

[pr2-eugin]

What is the Type of Document field in the Documents table?

 

[SirStevie3]

well the 'type' field shows if the document is one of 4 possible types i work with in this database. lets just say A, B, C, and D for now if thats ok.

i dont think it's related to this query though, as the only fields from the 'documents' table i'm using are the 'status' and 'document' fields. in fact, how did you know i had a field for 'type'?

(ps. if you cant tell i did a horrendous job naming my tables and fields and whatnot... if only i knew then what i know now....)

 

[pr2-eugin]

I am literally ROTFL.. That was purely hilarious, I beg your pardon..

Okay when I asked the Type what I actually meant was the Data Type of the Field Document. As there is always an alternative of Sum to get the Count value depending on the Data Type. What Data does the field hold, Numeric/Text/Date..

 

[SirStevie3]

as clearly obvious, i r noob.

the data type is text. the document names are numbers suchs as 4625-4228-7797.

my (probably not the best) logic was that i would filter the records based on the 'status' field of "No Record" and then it would just count how many records there are with "No Record". i just randomly chose the document field for the count because i knew that each record HAS to have a document name. so there was no way that field could be blank for a record.

 

[SirStevie3]

wait, count doesnt work the same way as sum does it?

I have many queries just like this one, except the filter is based on a different field. but the "count" field is the same across the board. i think the reason this is the only query that gives me an issue is because its also the only query where the count could result in 0. (well actually it results with null, i WISH it were a 0.)

for example, here's another query of the same setup with just a different filter:

SELECT Documents.Type, Count(Documents.Document) AS CountOfDocument
FROM [Request Details] INNER JOIN Documents ON [Request Details].Request_ID = Documents.Request_ID
GROUP BY Documents.Type, [Request Details].Contract, [Request Details].CDRL, [Request Details].Change_Cycle
HAVING (((Documents.Type)="PL") AND (([Request Details].Contract)=[Forms]![Report Runner]![Contract]) AND (([Request Details].CDRL)=[Forms]![Report Runner]![CDRL]) AND (([Request Details].Change_Cycle)=[Forms]![Report Runner]![ChangeCycle]));

i have 11 queries like these two, that im trying to combine into one query (a union query?) so that i can generate a report of all these counts. I understand this probably isnt the most efficient way of acheiving the results, but as a self-educated access user, this was the best solution i could come up with! haha.

thank you so much for your time and patience sir!

 

[pr2-eugin]

It would be really helpful if you could post a Stripped DB for us to look at..

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[SirStevie3]

ok i think i stripped it correctly....

there's only one table "documents" and one query "RR-NoRecordCount"

open up RR-NoRecordCount and it will only display column headers.

open up documents and change the "status" field of anyone one record to "No Record" and re-run the query. it works fine.

the problem is only when there are no "No Records" to be counted.

i need the query to show a "0" (zero) if there are no "No Records" to be counted.

_____________________
Thanks so much for your help!!!!

 

[pr2-eugin]

What I do not understand is, what you are trying to do. No Record will be counted only if the field is field has the Value 'No record'. What exactly do you mean by no record?

 

[SirStevie3]

sorry if i was unclear.

im using the phrase "No Record" as a status for a document, meaning our company has no physical record of that document. it does not refer to database records.

again... terrible naming... if i knew then what i know now...

this query and all my other "count queries" exist so that i can generate a report with a bunch of different statistics. since in this particular case there are no instances of a "No Record" status, the count is returning null.

but this messes up the union query that gathers all the counts into one consolidated query. if the union query gathers a null from just one of the count queries, it will disregard all the other successful counts, and only show column headers as if everything was null (which makes my report completely blank). so i need my count queries to show a 0 instead of a null. that way the union query (and in turn my report) function correclty.

 

[SirStevie3]

anyone have a solution? or an idea/suggestion? i've tried using Nz, but that didnt work

this has to be a simple question compared to some other threads on this forum...

also, happy friday!

 

[SirStevie3]

after hours of researching, i figured out a work around... which i wont take the time to explain now, but i've run into another road block :banghead:

this one isnt so bad though i dont think.

here's my SQL:

SELECT Count(*) AS [AL Count] FROM [RR-ALCount]
UNION ALL
SELECT Count(*) AS [AO Count] FROM [RR-AOCount]
UNION ALL
SELECT Count (*) AS [Denied Count] FROM [RR-DeniedCount];

This is great i'm starting to get all my counts in one place, but now... none of them have labels because they are all in one column...?

so my results look like this:

AL Count
149
54
14

instead of like this:

AL Count........AO Count........Denied Count
149...............54..................14

(excuse the periods, when using spaces the final post doesnt appear correclty)

can anyone help?

I need multiple columns because my report needs to display each count seperately with it's own title, as opposed to a list of untitled numbers lol...

Thanks so much for your help.