467,913 Members | 1,751 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,913 developers. It's quick & easy.

Form Filter Length

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
3 Weeks Ago #1

✓ answered by cactusdata

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)

  • viewed: 1832
Share:
11 Replies
isladogs
Expert 128KB
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
3 Weeks Ago #2
cactusdata
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)
3 Weeks Ago #3
64KB
Yes doesnt work, was hoping for a minor tweak. Thanks!
3 Weeks Ago #4
isladogs
Expert 128KB
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));
3 Weeks Ago #5
64KB
works now, missed that, thank you!!
3 Weeks Ago #6
isladogs
Expert 128KB
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!!
3 Weeks Ago #7
NeoPa
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 [].
3 Weeks Ago #8
isladogs
Expert 128KB
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
3 Weeks Ago #9
NeoPa
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?
3 Weeks Ago #10
isladogs
Expert 128KB
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....
3 Weeks Ago #11
NeoPa
Expert Mod 16PB
IslaDogs:
Totally confused now....
You & me both brother! That precisely describes my current understanding.
3 Weeks Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Pudlik, Szymon | last post: by
14 posts views Thread by Nothing | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.