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

[Deano]

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

[Matt.]

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

[Deano]

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));"

[Pieter Linden]

"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