[2008] Date parameter in MS Access WHERE clause - Can you show me the syntax?

[emaduddeen]

Greetings Everyone,

In the TableAdapter configuration wizard I am using this query:

SELECT        AttendanceID, StudentID, ClassId, DateOfClass, Absent
FROM            Attendance
WHERE        (DateOfClass BETWEEN #5/1/2010# AND #5/30/2010#)

Can you tell me the correct syntax to change the #5/1/2010# AND #5/30/2010# so the WHERE clause uses dteStartDate AND dteStartDate which are date variables ?

I tried this but it tells me I need to use the to_date function. dteStartDate is already a date variable so I know my syntax is wrong.

SELECT        AttendanceID, StudentID, ClassId, DateOfClass, Absent
FROM            Attendance
WHERE        (DateOfClass BETWEEN #" & dteStartDate & "# AND #" & dteEndDate & "#)

Thanks.

Truly,
Emad

[DataMiser]

Good question. I would think the format you tried would work in the case where you are using string variables.

Using date variables I would try omitting the # signs and see what happens.

[dglienna]

or something like this:

WHERE        (DateOfClass BETWEEN " & CDate(dteStartDate) & " AND " & CDate(dteEndDate))

Might need the second parameter to format it the way the DB is expecting it