Query to get data between dates is not considering all dates

[sathyaganapathi]

Hello,
I am using below query to get data from access table between two dates. But, always it is displaying dates one less than the eDate.

sDate - Start Date
eDate - end date

If Me.ComboBox1.Value = "ALL" And sDate <> "" And Me.ComboBox1.Value = "ALL" And eDate <> "" Then
    qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

for example If I select date 7/25/2021 as sDate and 7/28/2021 as eDate, data is displayed from 25th to 27th only. Data is available in database for 28th too. But it is not displayed. If both the dates are same, no data is displayed.
Please help.

 

[sathyaganapathi]

One more problem is, if I select only sDate, data is displayed for a day less than the sDate.
for example: if I select sDate as 26th, data is displayed for 25th.
Why is this and how to correct it? please help.

 

[ravi2628]

One more problem is, if I select only sDate, data is displayed for a day less than the sDate.
for example: if I select sDate as 26th, data is displayed for 25th.
Why is this and how to correct it? please help.

i think you have to select sdate ">=" and edate"<=" in the query

 

[sathyaganapathi]

Thanks ravi2628 for your inputs.
But, where and how to use it in the above code? I am new to VBA code and trying different options from hours. Can you please help?
Currently i am using 'between sDate and eDate'.

 

[ravi2628]

Thanks ravi2628 for your inputs.
But, where and how to use it in the above code? I am new to VBA code and trying different options from hours. Can you please help?
Currently i am using 'between sDate and eDate'.

If Me.ComboBox1.Value = "ALL" And sDate >= "" And Me.ComboBox1.Value = "ALL" And eDate <= "" Then
qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
End If

 

[sathyaganapathi]

Still the same probem continued. Is there any possibility to use '<=' and '<=' in the query line? I am not sure about it.

 

[Joe4]

Can you go to your "TBL_PlabInput" table and confirm the data type of the "Process_DateTime" field?

Also, see what this message box returns (it will show you the string you are building):

MsgBox "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"

Does it look correct to you?
Can you post for us what it shows?

 

[welshgasman]

You will likely have a time element in there.? BETWEEN is fine as it is.
So 29/07/2021 12:21:00 is greater that 29/07/2021 ?

Use DateValue() function to get JUST the date element.

 

[Micron]

AFAIK, any date field contains time. The default is 00:00:00 if you don't store an actual time value. If not formatted to show the time component, all you see is the date. So fields with zeros for time usually work with BETWEEN BeginDate AND <=EndDate (or when using >= <=). However, if the field contains actual time values, the cutoff point is midnight if you don't specify a time. Therefore, >= #01/01/2021# will not pick up anything later than 00:00:00 time (e.g. 01/01/20201 at 1 AM) so either add time to the date criteria or pick the next day after the day you want. I prefer to add the time as keeping users aware of the anomaly is always a problem. I'd use DateAdd function and add either the number of minutes from midnight until the end of the day (1440), or if you need more accuracy than that, add seconds (86400).

 

[sathyaganapathi]

Can you go to your "TBL_PlabInput" table and confirm the data type of the "Process_DateTime" field?

Also, see what this message box returns (it will show you the string you are building):

MsgBox "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"

Does it look correct to you?
Can you post for us what it shows?

Hi Joe4,
The data type of the "Process_DateTime" field is 'Data/Time'.
I have attached the message box result. for testing purpose I have selected date from 7/25/2021 to 7/28/2021.
After running the query I got the result in excel table. The first record is from '7/25/2021 12:02:00 AM' and the last record is from '7/27/2021 11:54:00 PM'. Stil I am missing the data for 7/28/2021.

there are 179 records from 7/28/2021 12:15:00 AM to 7/28/2021 10:42:00 PM in the access database table.
If I increase the end date to 7/29/2021 then data of 7/28/2021 is included in the result.

If I use the below code I am getting all the data from the access database.

If Me.ComboBox1.Value = "ALL" Then
qry = "SELECT * FROM TBL_PlabInput"
End If


below is the code I used to get data between two dates. I have to add few more conditions along with the below for filtering the data in different ways and combinations.

Dim qry As String, i As Integer
Dim sDate As Date, eDate As Date
Dim Source As String

Source = "xxxxxxx***InputDb.accdb" ' I have removed the full path name here.'

sDate = VBA.Format(Me.FromD.Value, "mm/dd/yyyy")
eDate = VBA.Format(Me.ToD.Value, "mm/dd/yyyy")
 
If Me.ComboBox1.Value = "ALL" And sDate >= "" And Me.ComboBox1.Value = "ALL" And eDate <= "" Then
    qry = "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
    
    MsgBox "SELECT * FROM TBL_PlabInput where Process_DateTime BETWEEN #" & sDate & "# AND #" & eDate & "#"
    
End If
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Source

    rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

    sh.Range("A2").CopyFromRecordset rst

Is there anything I am missing in the code? what else need to be considered to get the required data?