none
SELECT BETWEEN DATES VBA ACCES RRS feed

  • Question

  • Hi everyone, im started to coding in vba access and i need help to do a select between 2 dates. I've tried:

    1 - select * from table where DT BETWEEN #" & dtIni & "# AND #" & dtFim & "#"

    2 - select * from table where DT BETWEEN " & cdate(dtIni) & " AND " & cdate(dtFim)

    3 - dtIni = CDate(Format(bd.Fields("DT").Value, "dd/mm/yyyy hh:mm:ss"))

    select * from table where DT BETWEEN #" & dtIni & "# AND #" & dtFim & "#"

    and others and nothing works.

    in my database the field is set up as Date (dd/mm/yyyy).

    Could you guys help please?

    Tks for now...

    Sunday, March 29, 2020 7:08 AM

All replies

  • It would truly help if you provided your entire VBA procedure.

    You would do something like

    Sub Demo()
        Dim db                    As dao.Database
        Dim rs                    As dao.Recordset
        Dim sSQL                  As String
    
        On Error GoTo Error_Handler
    
        sSQL = "SELECT * " & vbCrLf & _
               "FROM table " & vbCrLf & _
               "WHERE DT BETWEEN " & Format$(dtIni, "\#mm\/dd\/yyyy\#") & " AND " & Format$(dtFim, "\#mm\/dd\/yyyy\#")
        Set db = CurrentDb
        Set rs = db.OpenRecordset(sSQL)
    
        With rs
            If .RecordCount <> 0 Then
                Do While Not .EOF
                    'do something with each record
                    .MoveNext
                Loop
            End If
        End With
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
        End If
        If Not db Is Nothing Then Set db = Nothing
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Demo" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End Sub

    That said, when dealing with dates in VBA I always use http://allenbrowne.com/ser-36.html

    So then you can then do something like

    Sub Demo()
        Dim db                    As dao.Database
        Dim rs                    As dao.Recordset
        Dim sSQL                  As String
    
        On Error GoTo Error_Handler
    
        sSQL = "SELECT * " & vbCrLf & _
               "FROM table " & vbCrLf & _
               "WHERE DT BETWEEN " & SQLDate(dtIni) & " AND " & SQLDate(dtFim)
        Set db = CurrentDb
        Set rs = db.OpenRecordset(sSQL)
    
        With rs
            If .RecordCount <> 0 Then
                Do While Not .EOF
                    'do something with each record
                    .MoveNext
                Loop
            End If
        End With
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
        End If
        If Not db Is Nothing Then Set db = Nothing
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Demo" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End Sub
    
    Function SQLDate(varDate As Variant) As String
        'Purpose:    Return a delimited string in the date format used natively by JET SQL.
        'Argument:   A date/time value.
        'Note:       Returns just the date format if the argument has no time component,
        '                or a date/time format if it does.
        'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
        If IsDate(varDate) Then
            If DateValue(varDate) = varDate Then
                SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
            Else
                SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        End If
    End Function


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, March 29, 2020 8:22 AM
  • 1 - select * from table where DT BETWEEN #" & dtIni & "# AND #" & dtFim & "#"

    Hi William,

    With date/time intervals I NEVER use the BETWEEN construction.

    It is better to use:  " WHERE DT >= " & As_date(start_daterange) & " AND DT < " & As_date(start_next_daterange).

    In this way you don't miss the records between    end_daterange    and    start_next_daterange.

    My function As_date is equivalent to Daniel's SQLDate, in which the date is formatted in ISO-format, and surrounded with the "#".

    Imb.

    Sunday, March 29, 2020 12:37 PM
  • I agree with the methodology, but I think the following is more intuitive for users:

    " WHERE DT >= " & As_date(start_daterange) & " AND DT < " & As_date(end_daterange) & "+1"

    Ken Sheridan, Stafford, England

    Monday, March 30, 2020 12:55 PM