Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-06-2019, 03:06 PM   #1
jeannier1975
Newly Registered User
 
Join Date: May 2018
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
jeannier1975 is on a distinguished road
VBA with decount for percentages

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

jeannier1975 is offline   Reply With Quote
Old 02-06-2019, 03:28 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,589
Thanks: 26
Thanked 475 Times in 451 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: VBA with decount for percentages

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
MajP is offline   Reply With Quote
Reply

Tags
microsoft access 2013

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentages % access2010 Tables 1 09-26-2014 10:43 PM
percentages hazell Queries 1 08-12-2013 04:24 AM
Percentages Spruk_77 Queries 12 11-19-2004 05:42 AM
Percentages dadrew Forms 8 09-29-2004 04:07 AM
Percentages Phillip Thomas Tables 1 05-09-2003 10:27 AM




All times are GMT -8. The time now is 10:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World