Filtering a date range when times (other than 00:00:00) are associated (1 Viewer)

dawsonrhodes

Member
Local time
Today, 07:19
Joined
Mar 8, 2020
Messages
85
Hey there,

Seems I am having an issue in the same realm as this below.


The feild I am trying to filter is defaulted to Now() when a user generates the record. It is a date/time format, and has differing times too. I would like to make a report that returns records based on a selected range.

It seems because of the times associated, MS Access is giving me a tough time. The current method I tried was this criteria

(Field being filtered: RoFDateCompleted) [date/time mm/dd/yyyy hh:mm:ss AM/PM]

>=[StartDate]<[EndDate]+1

But I also tried another method given here: http://allenbrowne.com/casu-08.html

Both not working due to the time, at least that is my suspicion.

As suggested in the first thread linked, I tried filtering with the 00:00:00 time added at the end, with no result, it shows all records still..

Any ideas?
 

Minty

AWF VIP
Local time
Today, 12:19
Joined
Jul 26, 2013
Messages
8,827
No matter what time component you could simply use

Where DateValue([RoFDateCompleted] >= [StartDate] and DateValue([RoFDateCompleted] <[EndDate]

As a criteria in the query.
In the immediate window

Code:
?datevalue (Now())
25/06/2021
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:19
Joined
Oct 29, 2018
Messages
16,599
>=[StartDate]<[EndDate]+1
Is that exactly what you put in the criteira? Or, were you missing the AND keyword?
 

dawsonrhodes

Member
Local time
Today, 07:19
Joined
Mar 8, 2020
Messages
85
No matter what time component you could simply use

Where DateValue([RoFDateCompleted] >= [StartDate] and DateValue([RoFDateCompleted] <[EndDate]

As a criteria in the query.
In the immediate window

Code:
?datevalue (Now())
25/06/2021
Hey there,

Tried this out, says it's missing syntax, tried adding 2 "))" at the end, didn't work.

Not sure where to place the code you added as well?

Thanks for replying!
 

Minty

AWF VIP
Local time
Today, 12:19
Joined
Jul 26, 2013
Messages
8,827
Hey there,

Tried this out, says it's missing syntax, tried adding 2 "))" at the end, didn't work.

Not sure where to place the code you added as well?

Thanks for replying!
Just to fix this it should have been

Where DateValue([RoFDateCompleted]) >= [StartDate] and DateValue([RoFDateCompleted]) <[EndDate]

I forgot the closing DateValue() bracket... :rolleyes:
 

Users who are viewing this thread

Top Bottom