Option to leave field blank in form (1 Viewer)

kmhoyt14

New member
Local time
Today, 12:00
Joined
Jan 28, 2020
Messages
8
It's been a few years (longer than I care to admit) since I've done VBA or access and I'm trying to relearn what little I knew so bare with me. I have a query that is based on unbound boxes in a form. I want the user to have the option to leave some of those boxes blank and the query just use whatever given information the user puts in the boxes. What's the best/ easiest functions to do this?
 

namliam

The Mailman - AWF VIP
Local time
Today, 18:00
Joined
Aug 11, 2003
Messages
11,696
Easiest solution is to bind the form to the table directly and have access to the "heavy lifting" for you.
 

Micron

AWF VIP
Local time
Today, 13:00
Joined
Oct 20, 2018
Messages
3,476
If this is a search form, it is perfectly normal for the controls to not be bound, yes?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
Select ....
From ...
Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null) AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fld3 Is Null) AND ....

Notice that each condition is compound and enclosed in parentheses. So, for each field you want to search by, the condition selects rows where that condition is true OR the condition wasn't specified on the form. The result is, if ALL the fields on the form are empty, then ALL the rows are returned. If you want to require the user to select at least one field, then you will need some code to check that before your code runs the query.
 

Micron

AWF VIP
Local time
Today, 13:00
Joined
Oct 20, 2018
Messages
3,476
Pat, isn't that the opposite of what was asked for?
and the query just use whatever given information the user puts in the boxes.
I take that to mean if the control wasn't filled in, don't use it in the criteria at all.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
Micron,
No. Take a look at the where clause. Each condition within parentheses either selects rows that match what was in the form field or ignores the condition when the form field is null.
 

Micron

AWF VIP
Local time
Today, 13:00
Joined
Oct 20, 2018
Messages
3,476
I did look at the clause and this is how I interpreted it:
- if field has a value, return records that match that value
OR
- if field is null, return records that contain null values in that field
Is that not correct?
 

kmhoyt14

New member
Local time
Today, 12:00
Joined
Jan 28, 2020
Messages
8
I want the query to return results that are selected from the form and if there are blank criteria then I want it to not use the criteria in that box. I was getting an error when I tried the Where function when I extended it to more than 1 criteria. When I had the Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) under one of the fields I tried to use it with my other two fields that are required and it returned all results for all three criteria individually rather than finding results that contained only the selected criteria.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
Micron,
the is null is referring to the selection field on the form, NOT the data field in the table. So, if the user doesn't put anything in fld3, then the criteria for fld3 will be ignored because of the "OR Forms!yourform!fld3 Is Null". That is why the parentheses are critical. If ( A or B) = if EITHER the first condition is true or the second condition is true, the condition inside the parentheses is true. Then you connect with other conditions using AND --
If (A or B) AND (C or D) AND (E or F) ----

kmhoyt,
Please post the entire query if you want us to debug it. Also, make sure you use your own form and field names when implementing my example.
 

Micron

AWF VIP
Local time
Today, 13:00
Joined
Oct 20, 2018
Messages
3,476
Pat, you're not getting it and I don't wish to debate this further. My point is along this line: one interpretation of the op is that IF the form control is blank, don't include it in the results. This would require looping through all the controls and has nothing to do with including it regardless of whether or not it is null. Your version includes it regardless of that fact. I'm not saying your interpretation is incorrect, but you and I might do less bantering if you tried harder to understand my points rather than arguing yours. I will leave you guys to this one.
 

kmhoyt14

New member
Local time
Today, 12:00
Joined
Jan 28, 2020
Messages
8
Below is the SQL of what I had before using the is null. This worked when I tested it on 3 criteria but then I extended it to all 6 criteria the search results would show nothing. Any and all help is appreciated.

SELECT [FM Data].Zone, [FM Data].Mixer, [FM Data].Line, [FM Data].Equipment, [FM Data].Type, [FM Data].Color, [FM Data].[Item#], [FM Data].Source, [FM Data].[FM Library#], [FM Data].[Part#], [FM Data].Attachments
FROM [FM Data]
WHERE ((([FM Data].Zone) Like "*" & [Forms]![SearchForm]![Zonetxt] & "*") AND (([FM Data].Mixer) Like "*" & [Forms]![SearchForm]![Mixertxt] & "*") AND (([FM Data].Line) Like "*" & [Forms]![SearchForm]![Linetxt] & "*") AND (([FM Data].Equipment) Like "*" & [Forms]![SearchForm]![Equiptxt] & "*") AND (([FM Data].Type) Like "*" & [Forms]![SearchForm]![Typetxt] & "*") AND (([FM Data].Color) Like "*" & [Forms]![SearchForm]![Colortxt] & "*"))
ORDER BY [FM Data].Zone, [FM Data].Mixer, [FM Data].Line, [FM Data].[Item#], [FM Data].[FM Library#], [FM Data].[Part#];
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
Micron, I DO get it. MY suggestion allows you to use a fixed query rather than building a custom one with only the criteria filled in on the form. This method is much simpler since it doesn't require code so I use it whenever i have only a few variables. If the search criteria is more complex, then I use VBA to build it. I've written my million lines of code. I don't need the practice. If I can do something with a query rather than code, I'll use a query.

kmhoyt,
LIKE is used for strings. If the new fields you added are NOT strings, then you may not get the results you expect.

The ONLY reason to ever use LIKE is if you actually are searching on partial text strings. You are using LIKE because it can make the criteria optional. If you are picking items from comboboxes on a form, you are NOT searching on partial text strings. Using LIKE prevents most query engines from optimizing your query by using indexes. So LIKE almost always forces a full table scan. That's fine when your table contains 500 records. Not so fine when it contains 500,000!!

What is happening with your method is the LIKE *'s are converting the null controls to just an * which satisfies the condition since * = anything.

My suggestion does NOT prevent the use of indexes so it is more efficient even though it does TWO tests for each condition.
(Test 1 = does the table field = the form field
OR
Test 2 = is the form field empty)

If EITHER condition is true, then the condition is true.

Micron is confusing you by arguing with me. Either try my suggestion or let him answer your question.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
Code:
WHERE ([FM Data].Zone = [Forms]![SearchForm]![Zonetxt] OR [Forms]![SearchForm]![Zonetxt] Is Null )
AND ([FM Data].Mixer = [Forms]![SearchForm]![Mixertxt] OR [Forms]![SearchForm]![Mixertxt] Is Null)
AND ([FM Data].Line = [Forms]![SearchForm]![Linetxt] OR [Forms]![SearchForm]![Linetxt] Is Null)
AND ([FM Data].Equipment = [Forms]![SearchForm]![Equiptxt] OR [Forms]![SearchForm]![Equiptxt] Is Null)
AND ([FM Data].Type = [Forms]![SearchForm]![Typetxt] OR [Forms]![SearchForm]![Typetxt] Is Null)
AND ([FM Data].Color = [Forms]![SearchForm]![Colortxt] OR [Forms]![SearchForm]![Colortxt] Is Null)
ORDER BY [FM Data].Zone, [FM Data].Mixer, [FM Data].Line, [FM Data].[Item#], [FM Data].[FM Library#], [FM Data].[Part#];
 

Users who are viewing this thread

Top Bottom