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

[dawsonrhodes]

Hey there,

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

Cannot filter on Date Fields in Access - MS Access Crashes

Good afternoon all, I'm struggling to find a solution to an issue I'm having when trying to right click and "filter for" on a FEW date columns in a linked table. This also happens in queries when i add a filter for the same fields. However when I filter on the same columns in a form, there...

www.access-programmers.co.uk

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]

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

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

 

[theDBguy]

>=[StartDate]<[EndDate]+1

Is that exactly what you put in the criteira? Or, were you missing the AND keyword?

 

[dawsonrhodes]

Is that exactly what you put in the criteira? Or, were you missing the AND keyword?

This is the exact copy of what I had in the criteria

 

[dawsonrhodes]

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

?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!

 

[theDBguy]

This is the exact copy of what I had in the criteria

So, what happens if you add the AND keyword?

>=[StartDate] AND <[EndDate]+1[/code

 

[dawsonrhodes]

So, what happens if you add the AND keyword?

>=[StartDate] AND <[EndDate]+1[/code

You're a genius, thank you, works perfectly fine now!

 

[theDBguy]

You're a genius, thank you, works perfectly fine now!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Minty]

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...