SQL WHERE Date in RST = Date Entered

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

Dim WrDate as String
Dim BgDate as Date

WrDate = "01/01/" & Me!TextYear & ""
BgDate = WrDate

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM Invoices WHERE
BILLDT > ' & #BgDate# & '")
I Have tried all methods to see if my billing date is
greater than the date entered.

WHERE BILLDT > '" & #BgDate# & "'
WHERE BILLDT > " & #BgDate# & "
WHERE BILLDT > '" & BgDate & "'
WHERE BILLDT > " & BgDate# & "
WHERE BILLDT > ' & BgDate & ' , Etc

I keep getting a syntax error but I do not work with Date
fields hardly at all.

WHERE BILLDT > #08/01/03# This works.

Does anyone know what the syntax is for just selecting
records based on dates using SQL?

Thank You in advance.
Antonio
 
Dim WrDate as String
Dim BgDate as Date

WrDate = "01/01/" & Me!TextYear & ""
BgDate = WrDate

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM Invoices WHERE
BILLDT > ' & #BgDate# & '")
I Have tried all methods to see if my billing date is
greater than the date entered.

WHERE BILLDT > '" & #BgDate# & "'
WHERE BILLDT > " & #BgDate# & "
WHERE BILLDT > '" & BgDate & "'
WHERE BILLDT > " & BgDate# & "
WHERE BILLDT > ' & BgDate & ' , Etc

I keep getting a syntax error but I do not work with Date
fields hardly at all.

WHERE BILLDT > #08/01/03# This works.

Does anyone know what the syntax is for just selecting
records based on dates using SQL?
Click to expand...
Put the #'s inside the string...

Set rst = db.OpenRecordset("SELECT * FROM Invoices " _
& "WHERE BILLDT > #" & BgDate & "#")

- Jim
 
Try this.
Set rst = db.OpenRecordset("SELECT * FROM Invoices WHERE
BILLDT >#" & BgDate & "#")

Here's a tip:
Use the query builder in access and look at the SQL
statement (the tool puts a lot of extra (( and )), but
what you have works fine.)

In your code, before you open the recordset,
use a msgbox or debug.print statement to view the SQL
for correctness.

msgbox "SELECT * FROM Invoices WHERE " _
& "BILLDT >#" & BgDate & "#"
 
You've received a couple of responses already showing you how to put the
quotes. I just wanted to point out a couple of other things.

First, there's a better way to get BgDate than what you're doing.

Dim BgDate as Date

BgDate = DateSerial(CLng(Me!TextYear), 1, 1)

(I hate relying on coercing the values!)

For that matter, you can simply use the value returned by the DateSerial
function in your SQL:

Set rst = db.OpenRecordset("SELECT * FROM Invoices WHERE BILLDT > " &
DateSerial(CLng(Me!TextYear), 1, 1))
 
Back
Top