Help with date Search constraint

[Viper]

Hello i can't get the correct form for a date as a constraint in SQL

here is my code

Search3 is a date that pulls from another text box... it pulls fine
When i run this code with search2 as the only constraint it runs fine

Set rst2 = db.OpenRecordset("SELECT * FROM " & "tbl_audits scheduled
WHERE [Rep] = '" & search2 & "'" And [Date] = #"& search3 &#")


I am having problems with:
#"& search3 &#"
Search3 This is a Date 11/21/2006

It says compile error
Expected: Expression

I think it is a problem with the way i have the #"&

 

[Douglas J. Steele]

You appear to be missing a quote before the final #.

In my opinion, though, you should use an explicit Format statement, in case
your users have their Short Date format set to dd/mm/yyyy:

Set rst2 = db.OpenRecordset("SELECT * FROM " & _
"[tbl_audits scheduled] WHERE [Rep] = '" & _
search2 & "'" And [Date] = " & _
Format(search3, "\#mm\/dd\/yyyy\#")

Note that you need square brackets around tbl_audits scheduled, because of
the space in the name.

(and you really should rename the field in from Date: that's a reserved
word, and using reserved words for your own purposes can lead to problems)

 

[Viper]

What is the proper form for a date

dim dateSearch as date

("SELECT * FROM " & " tblAUDITS where [Date1] = " # & dateSearch & #
")

What is the proper form for this constraint?
" # & dateSearch & # ")

I forget the form you need for dates

If it wasa a number it would be
dim search1 as long
("SELECT * FROM " & " [tbl_audits scheduled] WHERE [PDN] = " &
search1)

I just need an example of a date being used
Thanks

 

[Douglas J. Steele]

Dates need to be in mm/dd/yyyy format, and need to use # as delimiters.
(Okay, the mm/dd/yyyy format isn't absolutely correct: you can use any
unambiguous format, such as dd mmm yyyy or yyyy-mm-dd. The point is, you
cannot use dd/mm/yyyy, regardless of how your Regional Settings have defined
the Short Date format, because it'll be wrong for the first 12 days of each
month)

Use the format I suggested in my other post.