By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

A2k - Query error, concerning dates (quick fix i'm sure)

P: n/a
Ta for looking.

I am setting this SQL to the recordsource of a report but it's complaining
about a syntax error.

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON tblemp.ID =
tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between " & Datefrom & " And
" & Dateto & " AND ((tblemp.DateOfLeaving) Is Null));"

I'm hoping to cover a date range as specified by the user - i have captured
these dates in the variables Dateform and Dateto.

As I say my raw SQL is bad so any help much appreciated, deadline looming (:

thanks
Martin
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Whenever I'm using Dates in a query like this, I usually surround them with
#, like this:

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON tblemp.ID =
tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between #" & Datefrom & "#
And
#" & Dateto & "# AND ((tblemp.DateOfLeaving) Is Null));"

Hope that helps,

cheers,
Matt.
"Deano" <ma************@hotmail.com> wrote in message
news:wY*******************@wards.force9.net...
Ta for looking.

I am setting this SQL to the recordsource of a report but it's complaining
about a syntax error.

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON tblemp.ID =
tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between " & Datefrom & " And " & Dateto & " AND ((tblemp.DateOfLeaving) Is Null));"

I'm hoping to cover a date range as specified by the user - i have captured these dates in the variables Dateform and Dateto.

As I say my raw SQL is bad so any help much appreciated, deadline looming (:
thanks
Martin

Nov 12 '05 #2

P: n/a
Matt. wrote:
Whenever I'm using Dates in a query like this, I usually surround
them with #, like this:

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON
tblemp.ID = tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between #"
& Datefrom & "# And
#" & Dateto & "# AND ((tblemp.DateOfLeaving) Is Null));"

Hope that helps,

cheers,
Matt.


Thanks Matt, I included your amendments and then finally cracked it by
taking a leading bracket out just after the WHERE clause;

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON
tblemp.ID = tblAbsence.ID WHERE ((tblAbsence.AbsenceDate) Between #"
& Datefrom & "# And
#" & Dateto & "# AND ((tblemp.DateOfLeaving) Is Null));"
Nov 12 '05 #3

P: n/a
"Deano" <ma************@hotmail.com> wrote in message news:<wY*******************@wards.force9.net>...
Ta for looking.

I am setting this SQL to the recordsource of a report but it's complaining
about a syntax error.

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON tblemp.ID =
tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between " & Datefrom & " And
" & Dateto & " AND ((tblemp.DateOfLeaving) Is Null));"

I'm hoping to cover a date range as specified by the user - i have captured
these dates in the variables Dateform and Dateto.

As I say my raw SQL is bad so any help much appreciated, deadline looming (:

thanks
Martin

Martin,
you have to wrap the date fields in Date delimiters (#)

"SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,
tblAbsence.AbsenceReason, FROM tblemp INNER JOIN tblAbsence ON tblemp.ID =
tblAbsence.ID WHERE (((tblAbsence.AbsenceDate) Between #" & Me.Datefrom & "# And
#" & Me.Dateto & "# AND ((tblemp.DateOfLeaving) Is Null));"
HTH,
Pieter
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.