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.

 

[welshgasman]

So use Between edate + 1

 

[sathyaganapathi]

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.

Hi welshgasman,
I have assigned the input as below to sDate and eDate to check the result.

from
'sDate = VBA.Format(Me.FromD.Value, "mm/dd/yyyy")
'eDate = VBA.Format(Me.ToD.Value, "mm/dd/yyyy")

to
sDate = DateValue(Me.FromD.Value)
eDate = DateValue(Me.ToD.Value)

But, still the result is same. I am missing the data for last date in the slection range.

 

[sathyaganapathi]

So use Between edate + 1

Hi welshgasman,
Thanks for the input. I tried with it. It worked out !!.
But, still I am not clear what is wrong with the code which skips one day data? Is it not strange?
what else need to be checked to get it corrected. Please help.

 

[welshgasman]

| have explained it in post #8 ?
I'll try again. If you have a field with 29/07/2021 17:43 that is greater that 29/07/2021 00:00:00 which is what you have for your edate.
The time element is throwing you calc off, but when you know what is happening, then your can adjust for it.

As I mentioned you could compare to DateValue(field), then you would be comparing like for like.?
However if you add 1 to your edate, then it is good up to 29/07/2021 23:59:59 ?

Another way to think of it is
Today is really a value of 44406 as far as Access/Excel is concerned.
However as I type the date and time, Now() )is 44406.7422337963

? datevalue(date)
29/07/2021 
? cdec(date)
 44406 
? cdec(now())
 44406.7422337963

Now is it clear?

 

[sathyaganapathi]

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.

Dear welsgasman,
Thanks for the input.
I used the DateValue funtion as below.
sDate = DateValue(Me.FromD.Value)
eDate = DateValue(Me.ToD.Value)
Still the problem continues. As suggested I checked after adding 1 to eDate, the result showed the last date selected.
But, the problem is, if I go with only one date for the selection, no data is fetched !

 

[welshgasman]

You do NOT use it on your form controls, they already ONLY have the date element

You use it on the field(s). They are the ones that have the time element?

Might just be easier for you to add 1 to edate?

If you are only gong to go with one date, then build the sql string manually, or set low and high values for whatever you are not supplying?

 

[Micron]

But, still I am not clear what is wrong with the code which skips one day data? Is it not strange?

Not even an acknowledgement either.

 

[sathyaganapathi]

You do NOT use it on your form controls, they already ONLY have the date element

You use it on the field(s). They are the ones that have the time element?

Might just be easier for you to add 1 to edate?

If you are only gong to go with one date, then build the sql string manually, or set low and high values for whatever you are not supplying?

Dear welshgasman,

Thanks for your inputs.
I could get the required result by ading one to the edate (eDate+1 in case of both the dates) and incase of only one date I did 'edate = sdate+1'. This took care of both the entries..
Thanks a lot.

 

[sathyaganapathi]

Not even an acknowledgement either.

Dear Micron,
Sorry, I did not notice the inputs in the flow as there was other inputs. Extremely sorry for that. But, Later I saw your points and understood the consequences of using 'between' and 'date format'.. Thanks to you too for your valuable inputs..
Have good day..