Query Date Criteria

[LFC]

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

 

[SOS]

If you want the past month it should be:

Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()),Month(Date())-1,Day(Date()))

If you want the entire last month:

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):

Between DateSerial(Year(Date()), Month(Date())-1, Day(Date()) And Date()-1

 

[LFC]

option 3 is the one i am going for. I put that in and I got the exact same results.

 

[LFC]

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

 

[SOS]

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?

 

[LFC]

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;

 

[SOS]

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.

 

[LFC]

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.

 

[LFC]

I figured it out. For some reason someone put the date variable as text so it messed everything up.