Need zero to show up in column if no fault totals exist

[aldeb]

My problem is if there is no FaultTotals nothing shows up. I would still like for the Four Columns to show data and have a 0 in the FaultTotals.

How can I accomplish this?


In a nutshell I am totaling all Cosmetic Faults based on the SystemGroup CTWT and a Date Range. If there are
no Cosmetic CTWT Faults for the DateRange I want a zero. The query works great as long as there is at least
One FaultTotal for the Date Range.

SELECT "1-3" AS Truck, "Cosmetic" AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup
HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"))
ORDER BY Count(*) DESC;

 

[EMP]

I'm afraid you can't accomplish that.

When there's nothing to return, a query won't return anything at all.

^

 

[Moniker]

You can do a RecordCount on that and check for zero. If it's zero, throw up a message box and say no faults are found. Psuedo-coded:

strSQL = "SELECT '1-3' AS Truck, 'Cosmetic' AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)='Cosmetic') AND ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ('E010','C809','F001','C810','F187','A910','M173','M174')))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup
HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)='CtWT'))
ORDER BY Count(*) DESC;"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

If rs.EOF Then
    MsgBox "No Faults Found"
Else
    *Whatever you do if faults are found here*
End If

 

[aldeb]

I received some code from someone on another forum to help solve my issue. I had to modify it and I am now getting 0 in the TotalFaults Column where there are no faults for
the date range. I do not understand how or why. Can some of the experts out there
tell me how and why by looking at the code below.

SELECT "1-3" AS Truck, "Cosmetic" AS Category, 
Count(*) AS FaultTotals, "CTWT" 
AS [System Group]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") 
AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
And [Forms]![Queries_ReportsFRM]![EndDateTxt]) 
AND ((WorkUnitsFaultsMainTBL.BuildID) 
In ("E010","C809","F001","C810","F187","A910","M173","M174")) 
AND ((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"));