424,279 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Count records with multi criteria using VBA

P: 40
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?
5 Days Ago #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,424
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
5 Days Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,886
Jeannier1975,

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

P: 40
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
ScheduledStartDate
Actual FinishDAte
ActualFinisgHour
ActualFinish
ActualLaborHours
Lfc_PostAudit
LFCAuditSupervisor
EstamatedLAborHours
AssignedOwnerGroup
Status Date
StatusTime
LFCpostauditcomments
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.
5 Days Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.