Count records with multi criteria using VBA

[jeannier1975]

1 High Priority 0 Views Last Modified: 2019-02-13 Edit Question
I have many queries were simply to come up with a number that is a count of records with a specific criteria, to be used later in a calculation. Because those criteria are so lengthy, and change with each variation of the dataI am collecting, I’m not sure if an IIF structure would return the results you want. Crosstab queries can get quirky when fields are not available on different data runs. And to be honest, a crosstab query is really just a type of pivot table like in Excel, but not as flexible. I want to do most (if not all) of the count-collecting in VBA using recordsets or the DCOUNT() function. Then, dumping the values into a temporary table and base the report off of that. The queries I already have built are doing pretty much what I described, but when I try to link them together for your final report, if anything had missing or null values, it causes problems. Doing the work in VBA givesme much more flexibility in handling these situations.
But I do not know how to write up the function to do that how can u do what is above?

[PhilOfWalton]

A little bit more information on the tables you are using and the criteria would be helpful (preferably send an image of your relationship page, with all the fields showing).

Often if you have problems with Null values, use the Nz() function which converts Nulls to Zeros.

If you can avoid the use or temporary tables, that will save database bloat.

Phil

[twinnyfo]

Jeannier1975,

Yes you will have to provide much more information for us to understand what it is that you are trying to do.

[jeannier1975]

I have a table that comes from a SAP report.
the fields are
ID PrimaryKey
Work Order
Description
Location
WorkType
Status
LFC_AssetDept
Target Start
TArget Start Hour
ScheduledStartD ate
Actual FinishDAte
ActualFinisgHou r
ActualFinish
ActualLaborHour s
Lfc_PostAudit
LFCAuditSupervi sor
EstamatedLAborH ours
AssignedOwnerGr oup
Status Date
StatusTime
LFCpostauditcom ments
ReportedDate
ReportedTime




The first query counts all the records that have the following:

Work Types that are In ("PMINS","PMPDM ","PMREG","PMRT ")
Status LIKE *COMP
TargetStartdate >=DateAdd("h" ,-1,[Enter the Start Date]) And <DateAdd("h",23 ,[Enter the End Date]) (dates i am using for test are 11/25/18 and 12/1/2018)
ActualLAbor Hours <> 00:00
ActualStartDate >=DateAdd("h" ,-11.8,[Enter the Start Date]) And <DateAdd("h",23 ,[Enter the End Date]) (dates i am using for test are 11/25/18 and 12/1/2018)

Then I divide that count into the following QUERY GROUPED BY DEPARTMENT

QUERY TWO IS THE COUNT OF THE WQORKORDERS that have this criteria

worktype same as above
Status <> CAN
Target start date same as above.