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: 15
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, 5 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,566
Thanks: 73
Thanked 1,483 Times in 1,372 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 online now   Reply With Quote
Old 05-18-2019, 11:53 AM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,653
Thanks: 0
Thanked 405 Times in 402 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: 424
Thanks: 2
Thanked 85 Times in 81 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,653
Thanks: 0
Thanked 405 Times in 402 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: 424
Thanks: 2
Thanked 85 Times in 81 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,653
Thanks: 0
Thanked 405 Times in 402 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
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 05:03 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