How to select by date range? (1 Viewer)

[hclifford]

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 = Me.Text12
    endDate = Me.Text14
    
    Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & "#;"
    Me.RecordSource = Task
End Sub

How does one actually do the date range search?

Could it be because my timestamp actually has both date and time, that's why the date search is not working?

 

[pbaldy]

You can use this to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

You can use DateValue() around the field to exclude the time.

 

[hclifford]

You can use this to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

You can use DateValue() around the field to exclude the time.

Thanks for the advice Paul! Just to check, do I use the DateValue() around my variable name or the column name?

 

[namliam]

Private Sub Command16_Click()
    startDate = Me.Text12
    endDate = Me.Text14

Keeping default names for controls is a BAD idea, it will become a maintenance nightmare before you know it...

Since you use calander popups, atleast your textfields are proper dates, there is however the risk that they get converted to non-us-dates which could very well be the root of the problem here.

Also be smart and keep your SQL readable, may be some what overdone here, but never to early to addopt a best practice....

    Task = " SELECT * " & _ 
           " FROM Final " & _ 
           " WHERE Final.Timestamp BETWEEN #" & format(startDate, "MM/DD/YYYY") & "# " & _ 
                                     " AND #" & format(endDate, "MM/DD/YYYY") & "#;"

The datevalue would in this case be put around the Timestamp field, along the theory that you are inputting 11/25/2014 and 12/01/2014 and hope to select a record with the timestamp 12/01/2014 14:15:15.
Any dates without an explicit time component have a 0 as time component, i.e. 00:00:00
Anything with a time component will always be greater than the same date without a time component afterall 00:00:01 > 00:00:00

 

[Galaxiom]

You can use DateValue() around the field to exclude the time.

This would be an inefficient solution requiring a function to be applied to every record.

Better to search between the first date and 23:59:59 on the last date.

Also note the dates must be in mm/dd/yyyy format so unless your regional date setting matches this you will have to Format the criteria dates.

"SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & " 23:59:59#;"

"SELECT * FROM Final WHERE Final.Timestamp BETWEEN Format(startDate, "\#mm\/dd\/yyyy\#") & " AND #" & Format(endDate,"mm\/dd\/yyyy") & " 23:59:59#;"

 

[hclifford]

Thanks a million Namliam and Galaxiom! Managed to get what I want by combining both your advice!

And thanks for the advice on renaming controls, Namliam, I'll keep that in mind next time!