Solved Query By Form Not Working (1 Viewer)

Dark Stranger

New member
Local time
Today, 03:30
Joined
Mar 28, 2020
Messages
10
Access Newbie here again with another question on my project database. I am trying to set up a Query by form so users can quickly filter the project info table. When i enter the filtering criteria in one field it works well, but when I add a criteria into a second field it stops working. The base table for the query is tblProjectInfo. The form for the search is frmSearch. The controls on the form are WhatProjectStatus and WhatProjectNumber.
On the query, in the ProjectStatus field i wrote the criteria as Forms![frmSearch}![WhatProjectStatus] Or Forms![frmSearch[![WhatProjectStatus] Is Null
For the Project Number field I wrote Forms![frmSearch}![WhatProjectNumber] Or Forms![frmSearch[![WhatProjectNumber] Is Null

When I go back to design view on the query I see that Access has split up the criteria into several rows and even added additional columns with the Is Null criteria separated out. What am i doing wrong?
 

vba_php

Forum Troll
Local time
Today, 04:30
Joined
Oct 6, 2019
Messages
2,884
you posting the code or sql would help. this is a scenario where access is quirky, and it makes no sense literally. more than likely, if you are wanting one or the other when entering values in either control, you would have to do something like this:
Code:
WHERE TABLE.FIELD1 = Forms![frmSearch}![WhatProjectStatus] Or Forms![frmSearch[![WhatProjectStatus] Is Null

AND

TABLE.FIELD2 = Forms![frmSearch}![WhatProjectNumber] Or Forms![frmSearch[![WhatProjectNumber] Is Null
it's almost impossible to explain in shorthand what that does, but more than likely if you plug that code in your sql window and run a test on it, you'll see the result and if that is what you're looking for or not. it sounds to me like that is what you need.
 

plog

Banishment Pending
Local time
Today, 04:30
Joined
May 11, 2011
Messages
11,611
On the query, in the ProjectStatus field i wrote the criteria as Forms![frmSearch}![WhatProjectStatus] Or Forms![frmSearch[![WhatProjectStatus] Is Null...

You jammed 2 criterion into one field, the first one makes sense the other does not. Because you put it under ProjectStatus it evaluates it to this (in english):

WHERE ProjectStatus field is equal to the [WhatProjectStatus input] of the form
And
WHERE ProjectStatus field is equal to the [WhatProjectStatus] input is Null

That second criterion makes no sense. You start it off by comparing it to ProjectStatus in your query but then you put all the criteria on the WhatProjecStatus field of the form. You changed comparison fields midstream and screwed the computer up.

As a human, I'm not entirely clear what you are trying to do. I suggest you explain to us in simple english what you are trying to accomplish.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 28, 2001
Messages
26,996
When you say "Query by form" I have done that a bunch. But the questions are (a) How many fields are potentially involved and (b) How many of those fields are involved at the same time? A third question yjsy applies only if multiple fields are involved is: How is the search supposed to work when multiple fields are presented?
 

Dark Stranger

New member
Local time
Today, 03:30
Joined
Mar 28, 2020
Messages
10
The search form has 15 boxes: 2 text, 9 combo, 4 date (Project open - Start and end, and Project close -start and end). The most common search will be by date range, or by Project Leader name. The most likely combination search will be date range and project status (Active, Complete . . .), or project leader and status. I'd say that three filters is the most that will be applied at any one time. I would like the effects of the filters to stack - the results have to pass all the conditions.

Thanks
you posting the code or sql would help. this is a scenario where access is quirky, and it makes no sense literally. more than likely, if you are wanting one or the other when entering values in either control, you would have to do something like this:
Code:
WHERE TABLE.FIELD1 = Forms![frmSearch}![WhatProjectStatus] Or Forms![frmSearch[![WhatProjectStatus] Is Null

AND

TABLE.FIELD2 = Forms![frmSearch}![WhatProjectNumber] Or Forms![frmSearch[![WhatProjectNumber] Is Null
it's almost impossible to explain in shorthand what that does, but more than likely if you plug that code in your sql window and run a test on it, you'll see the result and if that is what you're looking for or not. it sounds to me like that is what you need.
Ok, this has me wigging out. I tried your coding (with the correct field names) It worked for a while, then stopped. I looked back at the SQL and Access changed it!!?????

This is what I typed:
WHERE tblProjectInfo.ProjectStatus = Forms!frmSearch![WhatProjectStatus] Or Forms!frmSearch![WhatProjectStatus] Is Null
AND
tblProjectInfo.ProjectLead = Forms!frmSearch![WhatProjectLead] Or Forms!frmSearch![WhatProjectLead] Is Null

I'm trying a test to see if I can filter on both the project status and project leader name. It worked for a few times then stopped.

I went back and checked the SQL and found out that Access changed it into this:

WHERE (((tblProjectInfo.ProjectStatus)=[Forms]![frmSearch]![WhatProjectStatus]) AND (([Forms]![frmSearch]![WhatProjectLead]) Is Null)) OR (((tblProjectInfo.ProjectLead)=[Forms]![frmSearch]![WhatProjectLead]) AND (([Forms]![frmSearch]![WhatProjectStatus]) Is Null))


Arrrrrrrgh!!!!!!!

I moved a few things around in what Access created and it seems to be working:

WHERE (((tblProjectInfo.ProjectStatus)=[Forms]![frmSearch]![WhatProjectStatus]) OR (([Forms]![frmSearch]![WhatProjectStatus]) Is Null)) AND (((tblProjectInfo.ProjectLead)=[Forms]![frmSearch]![WhatProjectLead]) OR (([Forms]![frmSearch]![WhatProjectLead]) Is Null))

However, I went back at the SQL and Access changed it to this monstrosity???

WHERE (((tblProjectInfo.ProjectStatus)=[Forms]![frmSearch]![WhatProjectStatus]) AND ((tblProjectInfo.ProjectLead)=[Forms]![frmSearch]![WhatProjectLead])) OR (((tblProjectInfo.ProjectLead)=[Forms]![frmSearch]![WhatProjectLead]) AND (([Forms]![frmSearch]![WhatProjectStatus]) Is Null)) OR (((tblProjectInfo.ProjectStatus)=[Forms]![frmSearch]![WhatProjectStatus]) AND (([Forms]![frmSearch]![WhatProjectLead]) Is Null)) OR ((([Forms]![frmSearch]![WhatProjectStatus]) Is Null) AND (([Forms]![frmSearch]![WhatProjectLead]) Is Null))

Why does it do this??? IT IS DRIVING ME CRAZY!!!!! (Sorry about the yelling.)
 

plog

Banishment Pending
Local time
Today, 04:30
Joined
May 11, 2011
Messages
11,611
The search form has 15 boxes: 2 text, 9 combo, 4 date

Trying to apply criteria in a query is the wrong approach. I suggest you either create a report to show users the results of the search or you load the results into a form. When you use either of those to deliver data to the user you can then use their associated open methods and construct a custom filter string:

Form - https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform

Report - https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport

On your form you place a 'Search' button. User fills in the filter criteria, clicks the button and then code runs on that button to open the form/report filtered to just the data they need to see.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:30
Joined
May 7, 2009
Messages
19,169
you can Always edit the SQL generated by the Wizard in SQL View.
 

isladogs

MVP / VIP
Local time
Today, 09:30
Joined
Jan 14, 2017
Messages
18,186
The search form has 15 boxes: 2 text, 9 combo, 4 date (Project open - Start and end, and Project close -start and end). The most common search will be by date range, or by Project Leader name. The most likely combination search will be date range and project status (Active, Complete . . .), or project leader and status. I'd say that three filters is the most that will be applied at any one time. I would like the effects of the filters to stack - the results have to pass all the conditions.

In situations like this, I build the SQL in code dynamically rather than attempt to do so using queries
You might find it useful to look at my two example apps in this link Multiple group and filter
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 28, 2001
Messages
26,996
I'm with Colin on this one. I would always build the search dynamically with that many requirements. And I have got to tell you that with that many AND/OR situations, the logic required to organize things correctly would be overwhelming. Please don't take this wrongly, but your search form is a bit ambitious. Your goal may be difficult to reach.

As to why Access modified your query, it did so because internally it is trying to iron out ambiguities. Something about what you are trying to do leaves it thinking there are alternatives. Did you do this manually in the query design grid? That wizard is NOTORIOUS for expanding things out on you, particularly if some combinations are available. Actually, I'm a bit impressed that it only added what looks like one extra layer of parentheses. I've seen worse.
 

Dark Stranger

New member
Local time
Today, 03:30
Joined
Mar 28, 2020
Messages
10
Hey guys. Thanks for the help. I followed your recommendations and looked for examples dynamic SQL search code. I found what I needed in a blog by Allan Browne. The example uses a continuous form with unbound text boxes in the header.

DS
 

Users who are viewing this thread

Top Bottom