Interesting development... (1 Viewer)

[TedSla]

Working on a new app with Access 365.

This morning queries with Start / End parameters fail on the EndDate criteria. Have to ask for one additional day to have the dataset return the needed data.

Example:

Between [Forms]![Report Date Range]![BeginDate] And [Forms]![Report Date Range]![EndDate] of 12/15/2022 through 12/21/2022 returns records through the 20th. Set the EndDate to 12/22/2022 and the records dated through the 21st are returned.

Same scenario when using >=#12/15/2022 And <=#12/21/2022#; have to use #12/22/2022# to bring forth the records dated 12/21/2022.

Any suggestions?

 

[Galaxiom]

Dates that include times are greater than the date at 00:00:00 on that date.
Nothing new or interesting there. Rookie mistake.

 

[TedSla]

Thanks for the assist!

 

[GPGeorge]

Working on a new app with Access 365.

This morning queries with Start / End parameters fail on the EndDate criteria. Have to ask for one additional day to have the dataset return the needed data.

Example:

Between [Forms]![Report Date Range]![BeginDate] And [Forms]![Report Date Range]![EndDate] of 12/15/2022 through 12/21/2022 returns records through the 20th. Set the EndDate to 12/22/2022 and the records dated through the 21st are returned.

Same scenario when using >=#12/15/2022 And <=#12/21/2022#; have to use #12/22/2022# to bring forth the records dated 12/21/2022.

Any suggestions?

All dates are always stored with both a date portion and a time portion. If you do not specify the time, the default value is used: 00:00:00 or midnight on that date.

You can apply formatting to dates so that they display that value in many different ways, as a matter of fact. However, the time portion is always involved in operations, such as comparing one date to another.

In your case, the time portion of the dates must be something other than the default. If you use Now(), rather than Date() to enter them, for example, the time portion is stored even if it's hidden by formatting. If the format on the field in question, or the control on the form or report where you display it, shows it as MM/DD/YYYY, or DD/MM/YYYY, that doesn't change the fact the actual value is greater than midnight on that date.

That's why your comparison has to include the following date to capture all of those post-midnight times on the end date.

 

[gemma-the-husky]

it's very fiddly though if you have dates with a time, and some without a time.

if you want December dates say and have a range from

between 12/01/2022 and 01/01/2023 this will work, but it will also return dates for Jan 1st with no time element.
whereas between 12/01/2022 and 12/31/2022 will not return any dates for 31st Dec WITH a time element, as you are finding.

You could change your query to just select the date portion dateportion: date(mydate), and then you can use between 12/01/2022 and 12/31/2022

 

[Gasman]

Could also use DateValue() ?

 

[ebs17]

' bad style, no use of indexes
WHERE DateValue(DateTimeField) BETWEEN [StartDate] AND [EndDate]

' better
WHERE DateTimeField >= [StartDate] AND DateTimeField < [EndDate] + 1

 

[Pat Hartman]

If you don't actually need the time for this field, it is best to just get rid of it.
Backup the database
Run an update query that replaces the value using DateValue() which pulls only the date portion.
Then find all the places you mistakenly used now and change them to Date()

Then you never have to worry about the problem again. If you don't fix the data, you will forever be working around the mistake

 

[TedSla]

Really appreciate all of the feedback.

Pat, after Galaxiom replied, I did the update you suggested.

How have a Recorded Created Now() field for record ('er, User) monitoring and an editable Transaction Date() in place.

All works well.

Again, thanks to all who replied.

Enjoy the Holidays!

 

[Pat Hartman]

You're welcome
So, you cleaned up the existing rows that included time and removed the time component?
Merry Christmas