How to use dropdown selections on a form to pass into a query

[Jon A]

I got this idea from the MSDN website, but can't get things working
quite right.

I have a listbox on one form. The listbox is populated by a query that
I put together in QBE. The query displays eight column, and gets four
of the values to use for searching from the form - there are four
dropdowns that are used to make selections and those selections will
limit the items displayed in the listbox.

Everything works OK as long as I have a selection in every one of the
four dropdowns. But sometimes that's not desirable - maybe a person
wants to list the items based on one or two selections. So if a combo
box is cleared, then nothing displays in the listbox at all.

I used the debugging window to make sure of the value of the combo box
when it is cleared. It has the value NULL.

Originally I set up my query with the following in the CRITERIA line
of QBE. Here is what it looked like for the [ItemType] control.

[Forms]![frm_SelectItem]![ItemType]

So I changed that to the following:

iif([Forms]![frm_SelectItem]![ItemType] Is Null, Like "*",
[Forms]![frm_SelectItem]![ItemType])

But that doesn't work. I still get no records returned when a combo
box is cleared but it works fine if there is a selection in every one.

Anyone can help please?

 

[[MVP] S.Clark]

Try:

IIF(Len([Forms]![frm_SelectItem]![ItemType]) = 0 , Like "*",
[Forms]![frm_SelectItem]![ItemType])

Sometimes the control isn't Null, so the code doesn't act like you want.
Testing the length of the answer sometimes helps get the desired behavior.

 

[Jon A]

Thanks for the suggestion but that didn't make it work
either.

If I use Debug.Print in the ItemType_AfterUpdate event,
which is where I have Me![ListItems].Requery, it prints out
ItemType = Null.

Also, if I quit Access and then restart and open the form
there is nothing shown in the listbox. I can make selections
in each of the dropdowns and still nothing is shown in the
listbox until I make a selection in the fourth one - then it
displays the records that match the criteria.

I tried a test: I took the criteria off of all the other
fields except the ItemType field. Then I have the Criteria
for the [ItemType] field set as follows:

IIf(Len([Forms]![frm_SelectItem]![ItemType])=0,Like "*",
[Forms]![frm_SelectItem]![ItemType])

When I make a selection in the ItemType dropdown it lists
every record with that Item Type, as it should. When I clear
the dropdown (select the text and hit Delete) then the
listbox displays nothing. None of the other fields in the
query have any criteria and only the [ItemType] field is
tied to the form control.

I also tried ... Is Null, ... in the above criteria with the
same results.

This is puzzling. Do I have things set up incorrectly?

JonA

 

[John Spencer (MVP)]

Well, before we solve this we need some more information on your fields and
their contents.

Are all the fields text? Does the field always contain data in every record?

If so, you can use criteria like the following.

Like NZ(Forms!frm_SelectItem!ItemType,"*")

If the field is text, but may contain nulls you can try
Field: SomeField
Criteria: Forms!frm_SelectItem!ItemType OR Forms!frm_SelectItem!ItemType is Null

The problem with that construct is that Access restructures it and with four
different sets of criteria, you might end up with a "query too complex" error.

Another method is to insure that the field you are testing is never null, by
applying the NZ function to the field.
Field: NZ(SomeField,"")
Criteria: Like NZ(Forms!frm_SelectItem!ItemType,"*")

Yet another method is to build the SQL and criteria on the fly using VBA.

 

[Jon A]

I found some info on a website that said when a combo box is
being used in a query like I am doing and the combo box is
NULL, then Access does not return any records. It's
counter-intuitive the way Access is designed but at least
that means I probably have things set up somewhat correctly.

I tried your suggestion for the following:
Forms!frm_SelectItem!ItemType OR
Forms!frm_SelectItem!ItemType is Null

This seemed to work mostly. At least now I can get the
listbox to display records without having to make selections
in all four combo boxes.

But I might try some of your other suggestions because with
the above implementation if I make a selection in Combo1 and
Combo2 it's fine. But if I make a selection in Combo1 and
Combo3, then it displays the records it found for Combo1 and
does not update for the selection in Combo3. Then if I make
a selection in Combo2, it displays correctly for the
selections in all three. So apparently the way the query got
constructed it won't allow for me to "skip" combos. But at
least I don't have to make selections in all of them.

I have strict validations on the data entries for the fields
in the table I'm searching on, and they can never be NULL.
The controls I set up on the form show values for fields
that will always have an entry in the record.

One field is a text field (Description). The other fields in
the table are lookups - I store the code in this table and
the text for that code is looked up in another table. So all
the other controls are 2 columns with the ID (code) being in
column 1 (hidden) and the text description (lookup) in
column 2.

The combo boxes are unbound controls with their data source
set to the appropriate lookup table. The query will get
passed the ID code - a number of type long.

Thanks a bunch. I think I am getting somewhere!

Well, before we solve this we need some more information on your fields and
their contents.

Are all the fields text? Does the field always contain data in every record?

If so, you can use criteria like the following.

Like NZ(Forms!frm_SelectItem!ItemType,"*")

If the field is text, but may contain nulls you can try
Field: SomeField
Criteria: Forms!frm_SelectItem!ItemType OR Forms!frm_SelectItem!ItemType is Null

The problem with that construct is that Access restructures it and with four
different sets of criteria, you might end up with a "query too complex" error.

Another method is to insure that the field you are testing is never null, by
applying the NZ function to the field.
Field: NZ(SomeField,"")
Criteria: Like NZ(Forms!frm_SelectItem!ItemType,"*")

Yet another method is to build the SQL and criteria on the fly using VBA.

I got this idea from the MSDN website, but can't get things working
quite right.

I have a listbox on one form. The listbox is populated by a query that
I put together in QBE. The query displays eight column, and gets four
of the values to use for searching from the form - there are four
dropdowns that are used to make selections and those selections will
limit the items displayed in the listbox.

Everything works OK as long as I have a selection in every one of the
four dropdowns. But sometimes that's not desirable - maybe a person
wants to list the items based on one or two selections. So if a combo
box is cleared, then nothing displays in the listbox at all.

I used the debugging window to make sure of the value of the combo box
when it is cleared. It has the value NULL.

Originally I set up my query with the following in the CRITERIA line
of QBE. Here is what it looked like for the [ItemType] control.

[Forms]![frm_SelectItem]![ItemType]

So I changed that to the following:

iif([Forms]![frm_SelectItem]![ItemType] Is Null, Like "*",
[Forms]![frm_SelectItem]![ItemType])

But that doesn't work. I still get no records returned when a combo
box is cleared but it works fine if there is a selection in every one.

Anyone can help please?

 

[John Spencer (MVP)]

Well with numbers that are ALWAYS there you can use criteria like the following.
Especially if you know the min and max of the range of values.

FieldName: MyNumberAlwaysThere
Criteria: Between CLng(NZ(Forms!YourFormName!ControlA,-99999999)) And CLng(NZ(Forms!YourFormName!ControlA,99999999))

And as far as I know, NULL is handled the same way in most SQL-compliant
databases. Null means Unknown, so if you compare two unknowns your result is
unknown. That is why the IS Null and IS NOT Null operations exist.

I found some info on a website that said when a combo box is
being used in a query like I am doing and the combo box is
NULL, then Access does not return any records. It's
counter-intuitive the way Access is designed but at least
that means I probably have things set up somewhat correctly.

SNIP

 

[Jon A]

OK!!! Got it working! Thanks.

Now, if you could give one more piece of advice.

One of the fields is a sequence number that just starts at 1
and increments by 1 for each child record that is added to
the parent record. When the items are displayed, it shows
all the child records that match the criteria, which is
correct. However, there are oftentimes more than one child
record and that means there are multiple listings of some
items.

What I'd like to do is to just return the latest or last of
the item records. The previous copies of the record will
never be used and so it can be confusing for the person to
figure out which one to select.

I tried clicking on the Totals button on the toolbar, which
gave another row in the query and then in the Total: row I
selected MAX for that field. But it doesn't do anything
different.

I remember someone asked about something like that a few
weeks back and there was an answer but I didn't think it
pertained to me so I didn't keep a copy of it. Now I can't
remember what the syntax was or what was said to set it up
correctly.

Any suggestions?