Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2019, 10:16 PM   #1
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Multiple query criteria

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.
Attached Files
File Type: zip test.zip (46.5 KB, 11 views)

JPR is offline   Reply With Quote
Old 05-17-2019, 05:29 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,834
Thanks: 78
Thanked 1,540 Times in 1,428 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Multiple query criteria

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-18-2019, 11:53 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Multiple query criteria

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 05-18-2019 at 12:05 PM.
June7 is offline   Reply With Quote
Old 05-18-2019, 04:08 PM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 614
Thanks: 3
Thanked 124 Times in 119 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Multiple query criteria

Quote:
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 05-18-2019, 04:36 PM   #5
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Multiple query criteria

According to MS https://docs.microsoft.com/en-us/off...ox.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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 05-18-2019 at 05:06 PM.
June7 is offline   Reply With Quote
Old 05-18-2019, 06:01 PM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 614
Thanks: 3
Thanked 124 Times in 119 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Multiple query criteria

Quote:
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 05-18-2019, 06:59 PM   #7
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Multiple query criteria

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-03-2019, 10:52 PM   #8
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Re: Multiple query criteria

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.
JPR is offline   Reply With Quote
Old 06-03-2019, 10:57 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,605
Thanks: 388
Thanked 622 Times in 603 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Multiple query criteria

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-03-2019, 11:17 PM   #10
JPR
Newly Registered User
 
Join Date: Jan 2009
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
JPR is on a distinguished road
Re: Multiple query criteria

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] & "*"
JPR is offline   Reply With Quote
Old 06-03-2019, 11:51 PM   #11
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,605
Thanks: 388
Thanked 622 Times in 603 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Multiple query criteria

Quote:
Originally Posted by JPR View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-04-2019, 12:12 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Multiple query criteria

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] & "*"

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Criteria in Query freidaf Queries 7 03-21-2018 10:03 AM
Query based on multiple criteria, show only results that match both criteria Scaniafan Queries 11 08-25-2017 05:08 AM
Multiple query criteria VBA Neilbees Modules & VBA 4 12-08-2013 02:23 PM
Question Multiple criteria in query leads to some criteria failing? booms General 3 10-09-2012 12:22 PM
How to add multiple criteria to query using VBA LB79 Modules & VBA 4 04-23-2009 02:53 AM




All times are GMT -8. The time now is 09:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World