How to give a zero value to count if zero

[Harris@Z]

Hi, wonder if someone can advise?
I have the following query which allows me to create a graph.
It results in the following results: Test, Interval and Count.

However if the count value is nothing, then no Interval is generated and then no data is generated and the graph generates an error message.
In other words, I would like to have the following result:
Test Interval Count
All 2022 01 0
All 2022 02 0
All 2022 03 0
All 2022 04 0
All 2022 05 0

This is the query:
SELECT IIf([SampleTestedFor].[Packed]=8,'','ELISA') AS Test, Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2) AS [Interval], Count([TestedFor]) AS [Count]
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Test_Methods.TestGroupCode)=2 Or (Test_Methods.TestGroupCode)=3))
GROUP BY Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2), SampleTestedFor.Packed;

Can someone assist please?

 

[arnelgp]

before showing the Graph, you test if there is "At least" a Count:

if Val(DSum("Count","YourTable") & "") <> 0 then
' show the graph
else
msgbox "No resulting data to show"
' if this is on the Open Event of the Form
Cancel = True
end if

 

[Harris@Z]

Thanks for your input.
I should have made the point that there are 3 other Select statements for 3 other Tests which are joined by a Union All Statement allowing the graph to show the Counts for each Test by Interval. So the statement above may be zero while the 3 others generate values

 

[XPS35]

if the count value is nothing, then no Interval is generated.....

Does your table Samples_List contains all possible intervals? And are the intervals you are missing not related to any SampleTestedFor record?
In that case use LEFT JOIN instead of INNER JOIN.

 

[Harris@Z]

The count is accurate, i.e., zero. Essentially my client selects a date range (not shown in the Select above), which may result in a positive Count, but if no Tests were done for that date range, will result in a Count of zero. But the problem is that it does not give a result of "0", but a query result with no values at all.

 

[plog]

The classic case of how NULL is not 0. 0 is a value, NULL is the absence of any value, even 0.

A query can only produce results for records in its underlying data sources. If there are no records, it produces nothing (NULL). So, to get your 0 result you need to somehow trick it into producing a record. XPS35 listed the way to do this--you need a table with all possible ranges you want to report on and then you LEFT JOIN the actual results to that interval table. That way every interval gets reported on--even if there are no results.

Do you have a table that lists all your intervals?

Additionally, you have a logical error in your SQL:

LEFT JOIN Test_Methods ON...
...WHERE (((Test_Methods.TestGroupCode)=2 Or (Test_Methods.TestGroupCode)=3))

When you LEFT JOIN a datasource and then apply criteria to that datasource you have undone the LEFT JOIN and turned it into an INNER JOIN. A LEFT JOIN means that you don't care if there are results in Test_Methods--show them anyway and just put NULL values in those fields. But your criteria doesn't let those NULL values pass through because only values 2 and 3 are allowed through. You undid the LEFT JOIN by applying criteria to a field in Test_Methods.

 

[Harris@Z]

Thanks plog and XPS35. I had not considered these points and so I will go back to the drawing table having been given a alternative way to do this.
Much appreciated