Search Between Dates/Times (1 Viewer)

NearImpossible

Registered User
Joined
Jul 12, 2019
Messages
149
I have a table that is storing the date and time that a transaction occurs in the following format "10/20/2019 5:57:11 PM"

I am trying to do a "Start" and "End" range search with

>=[forms]![TransferHistoryReports]![StartDate] for the start string And <=[Forms]![TransferHistoryReports]![EndDate] for the end string in my query.

My first transactions were done on 10/20/19 so If I enter a start date of 10/1/19 and end date of 10/20/19, nothing returns, however if I use an end date of 10/21/19, I get all the transactions from 10/20/19.

I am guessing this is due to the time being included in the stored value and not being entered in the search value.

As this report would need to include everything from 0:00:00 on the Starting Date through 11:59:59 on the End Date, i'm not sure how to include that in my query to make it work correctly.

Any help is appreciated, thank you


UPDATE: I am actually storing the Date/Time the first transaction occurred, as well as the last in separate columns so the table columns i'm use are TransferToDate and TransferFromDate and the query is >=[forms]![TransferHistoryReports]![StartDate] for the TransferToDate and <=[Forms]![TransferHistoryReports]![EndDate] for the TransferFromDate
 
Last edited:

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,798
My pc went nuts during my answer so ignore it. This has been an issue lately, but that's another story. Very frustrating.
What I was trying to say:
Without a time component the cutoff point is midnight (00:00:00) so anything after that isn't included. One fix I have seen is to add a day as suggested. However, if the user is providing dates, they may not know this and it is difficult to get consistency across the board. Then you have some adding a day and some not. Those that add it get too much data and aren't happy.

I've used DateAdd to add the number of minutes or seconds to just before midnight of the next day and went with the assumption that the normal user would specify the exact dates.
 
Last edited:

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I wasn't thinking of the user adding a day, but the code doing it in the background, so the user is none the wiser?

My pc went nuts during my answer so ignore it. This has been an issue lately, but that's another story. Very frustrating.
What I was trying to say:
Without a time component the cutoff point is midnight (00:00:00) so anything after that isn't included. One fix I have seen is to add a day as suggested. However, if the user is providing dates, they may not know this and it is difficult to get consistency across the board. Then you have some adding a day and some not. Those that add it get too much data and aren't happy.

I've used DateAdd to add the number of minutes or seconds to just before midnight of the next day and went with the assumption that the normal user would specify the exact dates.
 

NearImpossible

Registered User
Joined
Jul 12, 2019
Messages
149
Thanks to both of you for the response, not sure why I didn't think about bumping then end string by a day.

Changed the end string search to <DateAdd("d",1,[Forms]![TransferHistoryReports]![EndDate]) and it works perfect, thanks again.

Just checking to make sure, but is it safe to assume the starting date default time would be 00:00:00 or will it be the day selected and the current time?
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
If you are only supplying dates the the time element will be zero.?
So anything on that start date in the records will be greater than the date entered.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,798
the code doing it in the background, so the user is none the wiser?
That's what I said/meant.
If you are only supplying dates the the time element will be zero.?
Not in the field; without a time, the value of today as criteria is 11/23/2019 00:00:00 A.M. So any table date value like 11/23/2019 1:30:00 A.M. will not be included.
 
Last edited:

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,798
Thanks to both of you for the response, not sure why I didn't think about bumping then end string by a day.
I mean adding minutes up to midnight (1339 or 1440) but if that works for you then go for it

Just checking to make sure, but is it safe to assume the starting date default time would be 00:00:00 or will it be the day selected and the current time?
Start date (as criteria) will be midnight AFAIK.

My pc went nuts again, so apologies if this ends up double posting because miraculously, I ended up in this reply after a complete reboot. Won't know until I finish this one to see if my replies to Gasman uploaded. I may upload a vid somewhere in a bid to find anyone with a similar issue because when it does its thing, it's something to behold.

EDIT - I see they made it into my prior post (at the end).
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom