VBA Date Query

[Tieval]

Odd one here, I have a table with a date and time stored as a General Date.

I am importing data from text files where old ones have just a date so it comes in as 01/01/2018 00:00 and new ones with a time so it is imported as 01/01/2018 15:20.

If I want to query a date range of old data I have to use Between Date1 AND Date2 to get all data from correct days, if I want to query new data (including time) I have to use between Date1 AND (Date2 +1) to get all data from correct days.

How do I get data from correct days without it mattering whether it is old or new data?

 

[pbaldy]

Use DateAdd() to add 1439 minutes instead of a full day. You want to end up with

Between Date1 And Date2 23:59

go up to 23:59:59 if appropriate to your data.

 

[isladogs]

You could just use the date part and ignore the times.
OR change all time values of 00:00 to something like 00:30

 

[Tieval]

Well, thanks PBaldy, I did similar but added the correct seconds rather than minutes as follows:

BETWEEN Forms!frmMain!tStartDate And DateAdd('s',86399, Forms!frmMain!tEndDate)

All seems to work perfectly and the loss of a single second can't matter (?) as if you add it on it is 00:00 on the next day.

 

[pbaldy]

Happy to help.