I need some assistance folks on an issue that I can't get to function for me. I am trying to run a select query in A2K that has a WHERE clause of this:

WHERE (((tblMultiple.dtmDate) Between #9/1/2003# And #9/30/2003#))

That works great but I need this to be a dynamic date that uses date functions (DateAdd & DateSerial) to get the first and last day of the previous month and loads that into variables when the application starts - gdtmStart & gdtmEnd.

When I try to get this into the query as

WHERE (((tblMultiple.dtmDate) Between #' & gdtmStart & '# And #' & gdtmEnd & '#))

I get an error message stating that "The expression you entered has an invalid date value".

Could anyone help me to resolve this issue....???



Kenny Alligood

__________________
Kenny Alligood

Hi Kenny,

You need double quotes in a few more spots:

Private Sub Command5_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim gdtmStart As Date
    Dim gdtmEnd As Date
    Dim strSQL As String

    Set db = CurrentDb

    gdtmStart = Me.txtStartDate
    gdtmEnd = Me.txtEndDate

    strSQL = "SELECT ID, dtmDate FROM tblMultiple " & _
                "WHERE tblMultiple.dtmDate Between #" & gdtmStart & "#" & " And #" & gdtmEnd & "#;"

    Set rst = db.OpenRecordset(strSQL)

    Do Until rst.EOF
        For Each fld In rst.Fields
            Debug.Print fld.Value & ";";
        Next

        rst.MoveNext
        Debug.Print
    Loop

    rst.Close

End Sub

HTH,

Bob

Thanx Bob that worked the charm I needed!!


Kenny Alligood