MSAccess 2007 – replace Null with 0

Topic

New Reply

Hi guys.

I have a query that displays the total number of records from another query that counts them, and the total number of records that contain ‘Yes’ in one of the fields from another query. The issue is, if there are no records that contain ‘Yes’, I get null, when I actually need a ‘0’.

I will paste the SQL code below. Currently, when I run the query, it is asking me for a value for ‘CountofDB Part Number’, and what ever value I type in, shows up in the query.

Thank you in advance for your help!

KST

*********

SELECT qry_Order_List_BO_Count_of_All.[CountOfDB Part ID], IIf([CountofDB Part Number] Is Null Or [CountofDB Part Number]=””,0,[CountofDB Part Number]) AS My_Field
FROM qry_Order_List_BO_Count_of_All LEFT JOIN qry_Order_List_BO_Count_of_YES ON qry_Order_List_BO_Count_of_All.[Order ID] = qry_Order_List_BO_Count_of_YES.[Order ID];

Reply | Quote

Replies

Please show the queries that this query is based upon, it would be good to make the DB available so we can see what you are doing.

Reply | Quote

Thanks Patt! The query ‘qry_Order_List_BO_Count_Display’; I need a ‘0’ when there is no value.

Reply | Quote

Does this do the job?
SELECT qry_Order_List_Count_of_All.[CountOfDB Part ID] AS [Total List Count], qry_Order_List_Count_of_All.[Order ID], nz([CountOfPart Number],0) AS TotalYesCount
FROM qry_Order_List_Count_of_All LEFT JOIN qry_Order_List_BO_Count_of_YES ON qry_Order_List_Count_of_All.[Order ID] = qry_Order_List_BO_Count_of_YES.[Order ID];

Reply | Quote

Heck yes! That does it! Thank you Pat! You’ve helped me out before! I appreciate the wisdom! Have a great week! -Scott

Reply | Quote