specialist
Programmer
greetings-
I am trying to run a query which will count the number of records in a table but just cannot seem to get it working!
The background is this:
If a record is active, it has a 'Status' of '1', if it is pending, it has a status of '2'. What I am trying to acomplish is to get a count of all records which were active between a set time (StartDate and EndDate values from a form). For example, for historical analysis we would like to see the number of records that were open at the end of the quarter. So I would want to count all records with a status of either 1 or 2. The query I have below does not seem to capture any historical data. I tried to check all in year 2003 and returned the wrong value. My goal is to always display the records which were active during the date values.
If anyone could please check the query below and give me any advice I would GREATLY appreciate it.
Thank you for reading this post and for any assistance.
-Mike
------------------------------------------------------------
SELECT Count ('Entry') as prnRecs FROM tbl_Info
Set qryOffice4 = db.Execute(qryPri & "WHERE Category = 2 AND Status = 1 AND Location = ('Office') AND Initial_Date <= '" & EndDate & "' AND Complete = 1 OR Category = 2 AND Status = 2 AND Location = ('Office') AND Initial_Date <= '" & EndDate & "' AND Complete = 1 AND Category = 2 AND Status = 2 AND Location = ('Office') AND Close_Date >= '" & EndDate & "' AND Complete = 1 OR Category = 2 AND Status = 1 AND Location = ('Office') AND Close_Date >= '" & EndDate & "' AND Complete = 1")
I am trying to run a query which will count the number of records in a table but just cannot seem to get it working!
The background is this:
If a record is active, it has a 'Status' of '1', if it is pending, it has a status of '2'. What I am trying to acomplish is to get a count of all records which were active between a set time (StartDate and EndDate values from a form). For example, for historical analysis we would like to see the number of records that were open at the end of the quarter. So I would want to count all records with a status of either 1 or 2. The query I have below does not seem to capture any historical data. I tried to check all in year 2003 and returned the wrong value. My goal is to always display the records which were active during the date values.
If anyone could please check the query below and give me any advice I would GREATLY appreciate it.
Thank you for reading this post and for any assistance.
-Mike
------------------------------------------------------------
SELECT Count ('Entry') as prnRecs FROM tbl_Info
Set qryOffice4 = db.Execute(qryPri & "WHERE Category = 2 AND Status = 1 AND Location = ('Office') AND Initial_Date <= '" & EndDate & "' AND Complete = 1 OR Category = 2 AND Status = 2 AND Location = ('Office') AND Initial_Date <= '" & EndDate & "' AND Complete = 1 AND Category = 2 AND Status = 2 AND Location = ('Office') AND Close_Date >= '" & EndDate & "' AND Complete = 1 OR Category = 2 AND Status = 1 AND Location = ('Office') AND Close_Date >= '" & EndDate & "' AND Complete = 1")
