How to search by date range in Access?

[shawnnnnnnn]

Hi all,

I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.

Private Sub Command16_Click()
    Dim Task As String
    Dim startDate As Date
    Dim endDate As Date
 
    startDate = DateValue(Me.Text12)
    endDate = DateValue(Me.Text14)
 
    Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & Format$(startDate, "dd\/mm/yyyy") & "# AND #" & Format$(endDate, "dd\/mm\/yyyy") & "#;"
    Debug.Print Task
    Me.RecordSource = Task
End Sub

Could this be because my timestamp column actually contains both the date and time, that's why the search is unable to happen? If so, how do I work around this?

[jimatqsi]

Shawn,
I see you are formatting the date as dd\/mm\/yyyy instead of dd/mm/yyyy. I don't recognize that \ as meaning anything. I think if you remove it in both date formats the code will work.

The thing about date fields with time include is that the date/time value is greater than the date value alone. So you need to 1)know that every row has a time included and none are 0:00:00 and 2) you have to bump the ending date in your search up by 1 day.

Oh, one more thing. Do yourself a favor right now and commit to never using the automated names for your objects. Always give your objects recognizable names. "Command16" probably tells nothing about the function of that command button. When you, or someone else, looks at this code later, they'll have to search the form to figure out what Command16 is.

Jim