Date Variable in WHERE Clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tried several attempts to use a DATE variable in my WHERE clause and
continue to get data type mismatch. This is the last attempt...

dtDate1 = Forms!frminclusivedates!date1
dtDate2 = Forms!frminclusivedates!date2

Set Rst = MyDb.OpenRecordset("SELECT tblchecks.* FROM tblchecks WHERE " _
& "(((tblchecks.date) Between '# & dtDate1 & #' And '# & dtDate2 &
#'))")

Help Please
 
TheUbe said:
I have tried several attempts to use a DATE variable in my WHERE clause and
continue to get data type mismatch. This is the last attempt...

dtDate1 = Forms!frminclusivedates!date1
dtDate2 = Forms!frminclusivedates!date2

Set Rst = MyDb.OpenRecordset("SELECT tblchecks.* FROM tblchecks WHERE " _
& "(((tblchecks.date) Between '# & dtDate1 & #' And '# & dtDate2 &
#'))")

Help Please
Click to expand...

You've got your quote marks in a mess. Try this:

Set Rst = MyDb.OpenRecordset("SELECT tblchecks.* FROM tblchecks WHERE " _
& "(((tblchecks.date) Between #" & dtDate1 & "# And #" & dtDate2 &
"#))")

Note also that when you use dates directly in SQL, they need to be in US
format i.e. mm/dd/yy
 
Thanks so much-I just knew it was the quotes but just couldn't find the right
place to put them.
 
Thanks so much-I just knew it was the quotes but just couldn't find the right
place to put them.
Click to expand...

You might wish to reconsider your use of the word 'Date' as a field
name.
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
Back
Top