VBA with decount for percentages (1 Viewer)

jeannier1975

Registered User.
Local time
Today, 02:00
Joined
May 17, 2018
Messages
48
I want count-collecting in VBA using recordsets or the DCOUNT() function. instead of using a query. The queries I already have built are doing pretty much work , but when I try to link them together for your final report, if anything had missing or null values, it causes problems.query 1 numerator:
SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status) Like "*COMP") AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) AND ((MaximoReport.ActualLaborHours)<>"00:00") AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])));
and
query 2
SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReport.Status)<>"CAN") AND ((MaximoReport.[Target Start])>=DateAdd("h",-11.8,[Enter the start date]) And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])));

I divide thew two queries aND I GET THE PERCENTAGE
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:00
Joined
May 21, 2018
Messages
8,463
I reformatted to make easier to read.

I want count-collecting in VBA using recordsets or the DCOUNT() function. instead of using a query. The queries I already have built are doing pretty much work , but when I try to link them together for your final report, if anything had missing or null values, it causes problems.query 1 numerator:

Code:
SELECT Count(maximoreport.workorder) AS CountOfWorkOrder
FROM   maximoreport
WHERE  ( ( ( maximoreport.worktype ) IN ( "pmins", "pmor", "pmpdm", "pmreg",
                                          "pmrt" ) )
         AND ( ( maximoreport.status ) LIKE "*comp" )
         AND ( ( maximoreport.[target start] ) >=
               Dateadd("h", -1, [enter the start date])
               AND ( maximoreport.[target start] ) <
                   Dateadd("h", 23, [enter the end date]) )
         AND ( ( maximoreport.actuallaborhours ) <> "00:00" )
         AND ( ( maximoreport.actualstartdate ) >=
               Dateadd("h", -11.8, [enter the start date])
               AND ( maximoreport.actualstartdate ) <
                   Dateadd("h", 23, [enter the end date]) ) );
query 2

Code:
SELECT Count(maximoreport.workorder) AS CountOfWorkOrder
FROM   maximoreport
WHERE  ( ( ( maximoreport.worktype ) IN ( "pmins", "pmor", "pmpdm", "pmreg",
                                          "pmrt" ) )
         AND ( ( maximoreport.status ) <> "can" )
         AND ( ( maximoreport.[target start] ) >=
               Dateadd("h", -11.8, [enter the start date])
               AND ( maximoreport.[target start] ) <
                   Dateadd("h", 23, [enter the end date]) ) );
I divide thew two queries aND I GET THE PERCENTAGE
 

Users who are viewing this thread

Top Bottom