Solved Leaving comboboxes empty in a form. (1 Viewer)

knoet

New member
Local time
Today, 10:39
Joined
Mar 5, 2020
Messages
9
I have a form with 3 comboboxes to build a query.
It must be possible to leave 1 or 2 comboboxes empty.
I am using a function like this,this works fine for the first 2 comboboxes.
When I do the same for the 3th combobox nothing works anymore.
Code:
Like Nz([Forms]![frmSearch]![txtStatusinfo]; "*")
Any Suggestions.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:39
Joined
May 7, 2009
Messages
19,169
what is the 3rd combo/field type?
does your Locale separator is ;
 

knoet

New member
Local time
Today, 10:39
Joined
Mar 5, 2020
Messages
9
The locale separator is ;.
It seems that the field type isn't correct
I'll come back to it.
Thanks for the hint.
 

knoet

New member
Local time
Today, 10:39
Joined
Mar 5, 2020
Messages
9
Changing the field type solved the problem.
Great job.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Feb 19, 2002
Messages
42,970
1. When you are using comboboxes to select a value, NEVER use LIKE as the relational operator. LIKE is only used for searching for partial strings. With a combo, you are usually getting an ID or a full string value. When you use LIKE you almost always force the database engine to do a full table scan so instead of allowing it to use an index which it can search efficently, you make it look at each record individually. If your table is small, nothing you do matters but if you have a lot of data your query will eventually slow down.
2. To make the criteria optional, use the following method and pay special attention to how the parentheses are placed.

Where (fld1 = [Forms]![frmSearch]![txtfld1] OR [Forms]![frmSearch]![txtfld1] Is Null)
AND (fld2 = [Forms]![frmSearch]![txtfld1] OR [Forms]![frmSearch]![txtfld2] Is Null)
AND (fld3 = [Forms]![frmSearch]![txtfld1] OR [Forms]![frmSearch]![txtfld3] Is Null)

Since the expression contains both AND and OR operators, YOU must use parentheses to tell Access how to evaluate the expression just as you would if you were writing a mathematical expression that included both add/subtract and multiply/divide operations.

Just as a + b * c which evaluates as a + (b* c) is different from (a + b) * c
 
Last edited:

knoet

New member
Local time
Today, 10:39
Joined
Mar 5, 2020
Messages
9
Tested your suggestion Pat, works like a charm.
 
Last edited by a moderator:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Feb 19, 2002
Messages
42,970
Glad to help:)

Just something to think about. Using an index to find a record utilizes a binary search so any record in 1 million rows can be found by reading fewer than 20 records. Find the record at the mid point. If the value you are looking for is higher, use the higher half otherwise use the lower half. Split again use the higher or the lower half again, etc. So 20 reads or a million should help you to understand why you want to use indexes whenever possible.
 
Last edited:

Users who are viewing this thread

Top Bottom