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

[kvprajapati]

Maybe you need to use relational operators.

SELECT AttendanceID, StudentID, ClassId, DateOfClass, Absent
FROM Attendance
WHERE DateOfClass>=#" & dteStartDate & "# AND DateOfClass<=#" & dteEndDate & "#"

[emaduddeen]

Hi,

We found that we needed question marks.

Here's what we used:

WHERE        (DateOfClass BETWEEN ? AND ?)

Truly,
Emad

[johnwh]

Try This--
(TrainingRecord_1.[Date Taken]) Between "StartDate" And "EndDate")

This is the SQL code Access produces when creating a parameter query.
The result asks the user for a start date and then an end date, then calculated the results.

[emaduddeen]

Hi John,

Tried it but the error says "Unable to parse query text"

so we need to stick to the ? characters.

As soon as we used the ? characters the wizard created 2 parameters and all we needed to do when refreshing the TableAdapter was to user this:

Private Sub RibbonButtonRefreshReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonRefreshReport.Click
        ' Get the user chosen parameters ready for the TableAdapter.
        '-----------------------------------------------------------
        dteStartDate = DatePickerStartDate.Text
        dteEndDate = DatePickerEndDate.Text

        ' Load the data based on the date range entered by the user then show report.
        '----------------------------------------------------------------------------
        Me.AttendanceTableAdapter.Fill(Me.ISGLDataSet.Attendance, dteStartDate, dteEndDate)

        Me.ReportViewerAttendandeReport.RefreshReport()
    End Sub

Truly,
Emad

[kvprajapati]

You can either use ? or @parametername.

WHERE  (DateOfClass BETWEEN ? AND ?)

WHERE  (DateOfClass BETWEEN @dt1 AND @dt2)

[emaduddeen]

Greetings adatapost,

We tried WHERE (DateOfClass BETWEEN @dt1 AND @dt2) but got errors.

I prefer use the @ signs since it make the query easier to debug in the future.

Anyway, the error we get is in the attachment. Maybe the wizard needs some extra characters in order to use the @ signs.

Truly,
Emad