Query to show total count

[aldeb]

Below is the SQL of my query. This query shows total counts
of WorkUnits and Leaks. My dilemma is:

It only shows total count of workunits on days that leaks
show up. I still would like the total count of workunits
based on the daterange even if there are no leaks. Please
advise.

SELECT "4-5T" AS Truck, WorkUnitsFaultsMainTBL.TodaysDate, WU.[WU Totals], Count(*) AS [Leak Totals], (Count(*)/WU.[WU Totals]*1) AS [Percent]
FROM WorkUnitsFaultsMainTBL, (SELECT vTbl.Day as WUDay,
              Count(*) AS [WU Totals]
       FROM
             (SELECT DISTINCT Format(TodaysDate,'yyyy-mm-dd') AS [Day], WorkUnit
              FROM WorkUnitsFaultsMainTBL
              WHERE BuildID IN ('G004','E818','N005','F813','P005','G813')
               AND (TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                                    And [Forms]![Queries_ReportsFRM]![EndDateTxt])) AS vTbl
       GROUP BY vTbl.Day ) AS WU
WHERE (((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813","P005","G813")) AND ((Format([TodaysDate],'yyyy-mm-dd'))=[WU].[WUDay]))
GROUP BY WorkUnitsFaultsMainTBL.TodaysDate, WU.[WU Totals]
ORDER BY WorkUnitsFaultsMainTBL.TodaysDate, WU.[WU Totals], Count(*) DESC;

 

[namliam]

How about you remove your like "*leak*" where clause and use an Iif in the count to determain your count...

Count(IIF ( WorkUnitsFaultsMainTBL.Problem) Like "*leak*", 1,null)

 

[aldeb]

Mailman,

I added Count(IIF ( WorkUnitsFaultsMainTBL.Problem) Like "*leak*", 1,null) to a field of the query and get the following error:

The expression you entered has a function containing the wrong number of arguments

 

[namliam]

Sloppy copying on my part... Correct the )

Count(IIF ( WorkUnitsFaultsMainTBL.Problem Like "*leak*", 1,null) )

 

[aldeb]

Thanks Mailman,

This is what I needed.