472,811 Members | 1,808 Online

# Form Filter Length

72 64KB
Hi was wondering if this filter is ok to be shortened this way:

Month([DateProcessed])=1 AND Year(DateProcessed)=2021 OR Month([DateProcessed])=2 AND Year(DateProcessed)=2021 OR Month([DateProcessed])=3 AND Year(DateProcessed)=2021

changed to

Month([DateProcessed]) IN(1,2,3) AND Year(DateProcessed)=2021

Thanks
Feb 8 '21 #1

You could also use:

Expand|Select|Wrap|Line Numbers
1. DatePart("q", [DateProcessed])=1 AND Year(DateProcessed)=2021
or, to use an index on the field:

Expand|Select|Wrap|Line Numbers
1. [DateProcessed] Between DateSerial(2021, 1, 1) And DateSerial(2021, 3, 31)

11 2280
438 Expert Mod 256MB
I could just give a one word answer but...
The fact that you've asked the question suggests you understand enough to know what the answer is.
Please don't be offended but the easiest way to find out is just to test this for yourself.
Check whether the second method give the same results as the first and if so you've confirmed the answer
Feb 8 '21 #2
cactusdata
208 Expert 128KB
You could also use:

Expand|Select|Wrap|Line Numbers
1. DatePart("q", [DateProcessed])=1 AND Year(DateProcessed)=2021
or, to use an index on the field:

Expand|Select|Wrap|Line Numbers
1. [DateProcessed] Between DateSerial(2021, 1, 1) And DateSerial(2021, 3, 31)
Feb 8 '21 #3
Neruda
72 64KB
Yes doesnt work, was hoping for a minor tweak. Thanks!
Feb 8 '21 #4
438 Expert Mod 256MB
All you needed to do was put [] around the field in the Year part. This definitely works

SELECT Table1.ID, Table1.DateProcessed
FROM Table1
WHERE ((Month([DateProcessed]) In (1,2,3) And Year([DateProcessed])=2021));
Feb 8 '21 #5
Neruda
72 64KB
works now, missed that, thank you!!
Feb 8 '21 #6
438 Expert Mod 256MB
TBH so did I when I originally replied. Otherwise my one word answer would have been Yes!
I had to test it myself to see why it didn't work as written. Hoisted by my own petard!!
Feb 8 '21 #7
NeoPa
32,534 Expert Mod 16PB
I think you'll find the answer to the original question is a simple Yes.

Testing that may not always be straightforward - particularly as you left out the [] when you call the `Year()` function, but as you've done it the same way on both sides of the comparison then the answer is a simple Yes. They are equivalent.

PS. For my curiosity - Why would the [] be necessary in this case. generally speaking they are only necessary when :
1. A reserved word is used as the Field name.
2. The Field name is at all ambiguous.
3. The Field name contains white space characters or any other characters that could otherwise confuse SQL into treating the name as finished before the end.
I can't see how any of those scenarios pertain in this case so would expect the reference to work perfectly well, with or without the [].
Feb 8 '21 #8
438 Expert Mod 256MB
I agree with points 1 - 3 above and also thought the [] were superfluous until I tried it.
However, without the [] in the Year expression, the query editor put quotes around the field and gave a datatype mismatch error
Feb 8 '21 #9
NeoPa
32,534 Expert Mod 16PB
That's weird. When I tried it in Access 2003 it added [] instead.

I just tested in 2019 with the same results. How do you get quotes added?
Feb 9 '21 #10
438 Expert Mod 256MB
When I first tested this I just copied the WHERE clause from post #1 and it added them automatically to the Year expression.
I think I tested in A2010 but it may have been A365.

I've since tried typing out exactly the same expression and Access accepted it as written.
I've also tried just omitting the [] brackets in both expressions - again it worked fine.
Totally confused now....
Feb 9 '21 #11
NeoPa
32,534 Expert Mod 16PB