472,811 Members | 1,808 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,811 software developers and data experts.

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

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

11 2280
isladogs
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
isladogs
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
isladogs
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
isladogs
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
isladogs
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
IslaDogs:
Totally confused now....
You & me both brother! That precisely describes my current understanding.
Feb 9 '21 #12

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

Similar topics

12
by: Pudlik, Szymon | last post by:
Hi, I've written some code: function onSubmit(form){ for (var i = 0; i < form.elements.lenght; i++){ if (form.elements.disabled == 1) form.elements.disabled = 0; }
14
by: Nothing | last post by:
I have a form that has a Tab Control on it. On one of the tab control pages, there are only 3 pages, is a combo box based on a field called departments in a table. When the user selects one of...
0
by: Simon | last post by:
Dear reader, Under Tools>>Options>>Tables/Queries there is a setting possible of "SQL Server Compatible Syntax (ANSI 92)". The two tick boxes here are "This database" ...
0
by: EddiePhoenix | last post by:
This is the problem. When running a form filter on a form in acces instead of getting the form back with no data the page is returned filled white. This typically happens when a double filter is...
2
by: Henrootje | last post by:
Hello there! I have a piece of code in a module (function) that constructs a filter. But now I run into a problem..... It seems that I can only set a filter form a module if I open the form...
5
by: phill86 | last post by:
Hi, I have a form that I have applied a filter to by using combo boxes which works fine. Is there a way to apply that filter to the forms underlying query Here is the code that I use to...
2
by: phill86 | last post by:
Hi, I am filtering a report using the form filter with the following code DoCmd.OpenReport "report1", acViewReport, , Me.filter Which works fine untill I filter the form on one of the...
7
by: munkee | last post by:
Hi all, I am using Allen's excellent form filter script to filter the results from a query. I would now like to add some further functionality. How do I go about displaying say the top N costs...
0
by: JpjVB | last post by:
Hi, I've developed a form filter that produces a number of hours budgeted by employee/location/practice group/experience level and date available using some Allen Browne code. I have two command...
3
by: rwalle | last post by:
Hi I have a strange problem in a form filter my set up is as follows there is a form named "InAlarmResumeForm" that has a subform "AlmActiveQrySform" the form has Textboxes with datapicker enabled...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.