all records with dates less than? how to 1

[johnnyv]

I am trying to pull all records with dates less than a given date here is the sql I have created

strSQL = &quot;INSERT INTO ReportTableTemp SELECT * FROM [Billinginfo Query] Where [JobId] = &quot; & JobId & &quot; And [JobDate] < &quot; & MaxDateReport & &quot;&quot;

which generates the following statement

INSERT INTO ReportTableTemp SELECT * FROM [Billinginfo Query] Where [JobId] = 18 And [JobDate] < 11/19/2002


I have records in the table Billinginfo Query with dates less than 11/19/2002 and these dates are stored in the same format as '11/19/2002'

but the statement does not work, in other words it does not 'pull' any records out of the table

why

thanks

 

[Foada]

Depending on your field types and your database server you will need to add single quotes around your variables

[JobDate] < '&quot; & MaxDateReport & &quot;'&quot; If you choose to battle wits with the witless be prepared to lose.

 

[fulbert]

Yep that's correct, but in dealing with Date I prefer adding &quot;#&quot; around the variables

[JobDate] < #&quot; & MaxDateReport & &quot;#&quot;

 

[johnnyv]

Excellent works well thanks guys

 

[johnnyv]

One question if I could
I now have the less than a certain date working but when I change the requirement to <= a certain date it only returns the dates less than a certain date
here is my sql statement

strSQL = &quot;INSERT INTO ReportTableTemp SELECT * FROM [Billinginfo Query] Where [JobId] = &quot; & JobId & &quot; And [JobDate] <= #&quot; & MaxDateReport & &quot;#&quot;

 

[fulbert]

Your MaxDateRepor variable, I think, correct me if I'm wrong is formatted into an &quot;MM/DD/YYYY&quot; date format, so the value would be eg. 11/19/2002, right? But the underlying value of your [JobDate] field would be probably formatted into a &quot;MM/DD/YYYY hh:mm:ss AMPM&quot; so the value would be 11/19/2002 12:12:12 AM. That's different, tha's why you're always getting the results which are < (less than) and not <= (less than or equal to), right? You can check your database if it is formatted in &quot;MM/DD/YYYY hh:mm:ss AMPM&quot;, if that's the case, for me, I would do this approach:

1st approach, in saving a record make sure that you format the value of your date into a &quot;MM/DD/YYYY&quot; date format before adding or updating it in the database. You can use Format function, I hope you know hot to use it.

2nd approach is try to change the format of your MaxDateRepor variable, change it into what ever the date format of your [JobDate] field.

I would prefer the 1st approach, unless you're very specific in date and you want to include the time in you criteria.