Multiple query criteria (1 Viewer)

JPR · May 17, 2019

Hello,

I have a form with a list box that gets populated according to the selection made in dynamic combo boxes. The list box is updated starting from the selection in the combo named cbofilterStudies.
In my examples, please look at the selection “Age Study”.
I am having a problem with the query as cannot work out how to add multiple criteria.

In my sample db, you will notice that the last combo (which I have named txtRP) has 4 different selections. The list box only gets update with the first two but not with the others. Not sure how to manage this query.
My idea was to query at least 4 different fields in the table.
The other issue is that the txtcount1 textbox that counts the number of records in the list box. I have used the -1 as it always counts an extra records but if no records are selected, I get a -1.

Thank you for any help.

The_Doc_Man · May 17, 2019

You want to search this forum for the topic "Cascading Combo Boxes" which will give you several discussions and links about how to do multiple criteria for a search.

June7 · May 18, 2019

Not seeing any code to requery txtRP combobox.

Also, consider:

Me.txtcount1 = Me.lstStudy.ListCount - IIf(Me.lstStudy.ListCount = 0, 0, 1)

The reason for -1 is because column headers are counted as a row but when there are no records the column headers don't show so the count is 0. Conditional expression handles that.

Micron · May 19, 2019

when there are no records the column headers don't show

Is that behaviour peculiar to any particular set of design parameters? I'm fairly certain I've seen table/query based list boxes that still show the header when the table has no records.

June7 · May 19, 2019

According to MS https://docs.microsoft.com/en-us/office/vba/api/Access.ListBox.ColumnHeads:

"Headings in combo boxes appear only when displaying the list in the control."

However, I just looked at a db where I am using 4 listboxes with column headers to display related data. One record shows no data in the listboxes but 3 of the listboxes still display headers. Even weirder, on new record row all 4 listboxes show headers.

All 4 listboxes have an SQL statement as RowSource, however, the 3 that always show headers pull from a table whereas the fourth pulls from a query object.

OP db uses code to change the listbox RowSource property to reference a dynamic parameterized query object name.

Micron · May 19, 2019

I have a form with a list box

I read that earlier and was focused on listbox whereas I think you were referring to combo. However, I interpret "Headings in combo boxes appear only when displaying the list in the control." to mean they are not displayed until the combo list drops down, not that they are not visible if there are no records.

June7 · May 19, 2019

Ooops, crossed wires

. Yes, that one statement is about comboboxes not listboxes. Thanks for catching that.

However, everything else I said is all about listboxes.

JPR · Jun 4, 2019

Thank you for your help and sorry for the delay in getting back to you.

I have looked through several threads and have found a "partial" solution to my problem.

My form (frmMenu) has now an unbound list box (lstStudy) and 7 combo boxes used to populate and filter the list box as used as criteria for my query.

For each combo, I have create a textbox which duplicates the selection.
For example for cbo1, I have created txt1 with control source
=[cbo1].column(0).

In the query fields rows, I have the following criteria in the same rows:

Criteria: Like (txt1] & "*"
Or: Is Null

On the after update events of the combos, I have the following:

me.lstStudy.requery

Three list boxes are mandatory while for the other 4, users have the option to make a selection or not (the reason why I have added the Or Is Null.

My problem is now when I want to create a form or report based on the results of the list box (a form/report with records set to my query).

If I run the query, I get a pop up with the name of the txt1 and in input box. This happens for all the different text boxes.

Is it because this data is not saved anywhere?

Thank you for any help.

Gasman · Jun 4, 2019

PMFJI,
You need to refer to the form name as well for the controls.?
Use the builder in the QBE to get the correct syntax.
Alternatively set global variables/tempvars from those controls before calling the query.

HTH

JPR · Jun 4, 2019

Yes. Works great now. My mistake, just overlooked a simple but important detail.
My I ask just one more thing?
What is the reason of the & "*" after the criteria? Thanks

Like [forms]![myform]![txt1] & "*"

Gasman · Jun 4, 2019

JPR said:
Yes. Works great now. My mistake, just overlooked a simple but important detail.
My I ask just one more thing?
What is the reason of the & "*" after the criteria? Thanks

Like [forms]![myform]![txt1] & "*"

That allows you to select everything that begins with your control value.
If you wanted to search for it anywhere then you would use

Code:

Like "*" & [forms]![myform]![txt1] & "*"

HTH

isladogs · Jun 4, 2019

If you have a field that will be searched regularly then you should index that field as it will speed up searches dramatically. Typically 250 times faster in my tests. The effect is more noticeable when you have a lot of records.

However I suggest you avoid using a leading wildcard if possible as that will mean the indexing will be ignored. In other words, unless necessary use

Code:

Like [forms]![myform]![txt1] & "*"

rather than

Code:

Like "*" & [forms]![myform]![txt1] & "*"

Multiple query criteria (1 Viewer)

JPR

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

June7

AWF VIP

Micron

AWF VIP

June7

AWF VIP

Micron

AWF VIP

June7

AWF VIP

JPR

Registered User.

Gasman

Enthusiastic Amateur

JPR

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread