jeannier1975
Registered User.
- Local time
- Today, 04:30
- 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
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