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
