Query Date Criteria

LFC

Registered User.
Local time
Today, 13:02
Joined
Jul 22, 2010
Messages
43
I currently have a query that should be pulling everything from the past month. I have it extremely close except for some reason it pulls in 6/4/2010, 6/7/2010, 6/8/2010, 6/9/2010. My criteria in the date field is Between Date() And DateSerial(Year(Now()),Month(Now())-1,Day(Now())). Any idea what is wrong?

Thanks
 
If you want the past month it should be:

Code:
Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()),Month(Date())-1,Day(Date()))
If you want the entire last month:

Code:
Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()),Month(Date()),0))

Or if you want the month (like if today is the 30th and you want from the 30th of June to the 29th of July):

Code:
Between DateSerial(Year(Date()), Month(Date())-1, Day(Date()) And Date()-1
 
option 3 is the one i am going for. I put that in and I got the exact same results.
 
it's wierd because there is data for the days before and after 6/9/2010 that doesn't get pulled in. the date is the only criteria i have...i'm confused
 
it's wierd because there is data for the days before and after 6/9/2010 that doesn't get pulled in. the date is the only criteria i have...i'm confused

What's the actual SQL for the query?
 
SELECT Torque_Readings_tbl.Job_Order_Number, Torque_Readings_tbl.Audit_Date, Count(Torque_Readings_tbl.Job_Order_Number) AS CountOfJob_Order_Number, Torque_Job_Order_tbl.Day_Freq, Torque_Job_Order_tbl.Dept_Num
FROM Torque_Job_Order_tbl INNER JOIN Torque_Readings_tbl ON Torque_Job_Order_tbl.Job_Order_ID = Torque_Readings_tbl.Job_Order_Number
GROUP BY Torque_Readings_tbl.Job_Order_Number, Torque_Readings_tbl.Audit_Date, Torque_Job_Order_tbl.Day_Freq, Torque_Job_Order_tbl.Dept_Num
HAVING (((Torque_Readings_tbl.Audit_Date) Between DateSerial(Year(Date()),Month(Date())-1,Day(Date())) And (Date()-1)))
ORDER BY Torque_Readings_tbl.Audit_Date DESC;
 
Well as long as there are records corresponding to each Job Order Number then it looks right to me. Is there a way you can upload a copy of the database but with any of the identifying data changed to bogus data? Then we might be able to see what is happening.
 
Well, I have no idea what happened, but it's working like a charm now and i don't think I had changed anything. Thanks for the help.
 
I figured it out. For some reason someone put the date variable as text so it messed everything up.
 

Users who are viewing this thread

Back
Top Bottom