Form Filter Length

[Neruda]

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

[isladogs]

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

[cactusdata]

You could also use:

DatePart("q", [DateProcessed])=1 AND Year(DateProcessed)=2021

or, to use an index on the field:

[DateProcessed] Between DateSerial(2021, 1, 1) And DateSerial(2021, 3, 31)

[Neruda]

Yes doesnt work, was hoping for a minor tweak. Thanks!

[isladogs]

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));

[Neruda]

works now, missed that, thank you!!

[isladogs]

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

[NeoPa]

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

[isladogs]

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

[NeoPa]

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?

[isladogs]

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

[NeoPa]

IslaDogs:
Totally confused now....

You & me both brother! That precisely describes my current understanding.