Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-03-2014, 07:23 AM   #1
compton clerk
Newly Registered User
 
Join Date: Aug 2014
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
compton clerk is on a distinguished road
Multi criteria combo query problem

Hi.
I hope someone may be able to help with query multi-criteria problem I am so far unable to overcome.

My database is for recording the decorations and contents of apartments and houses.

Each apartment or house has an address. Within each address there are Areas (rooms) and Parts (parts of an Area which are then described)

On my main form there is an address ID field

I have two combos to search / filter the current address data which returns the search / filter on a data entry sub form.

Both combos return information from the same table (Inventory)

I have one combo where an area can be selected via a query. (Works OK) (cboArea)


The second combo (cboPart) displays Parts (parts of the pre selected Area) This is the problem query.

The second combo query (based on the choice in the first combo) successfully returns only applicable parts for the area previously selected in the first combo for the current address.

Problem. It is convenient within the concepts of the database to be able to click the second combo without making a choice from the first combo as in this circumstance all the 'Parts' from the searched table are displayed. This for example is so I can work on (have returned) all of the ceilings in all of the current address Areas (rooms)

However the second combo used without making a choice in the first combo returns all 'Parts' for all addresses in the table being searched regardless of the current property address.

My question is:

Is there a criteria for the second combo I can add which will only return all Parts for the current address when nothing is selected from the first combo but still allow the current filtering when a choice is made from the first combo.

Here is the SQL statement in question for the second combo


SELECT DISTINCT Inventory.Part, Inventory.AddressID, [AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null AS Expr1
FROM Inventory
WHERE ((([AddressID]=[Forms]![InventoryReport]![txtInvID] Or [Forms]![InventoryReport]![txtInvID] Is Null)=True) AND

((Inventory.Area)=[Forms]![InventoryReport]![cboArea])) OR ((([Forms]![InventoryReport]![cboArea]) Is Null))
ORDER BY Inventory.Part;

i would like to learn how to express the following for combo two:

If an area choice is selected from combo one (cboArea) display all the parts in the table for that area filtered by the current address ID
If an area choice is not made from combo one (cboArea) combo two to return all the parts in the table but only for the current address.

Apologies for being long winded.
Hoping someone may be interested to help.

compton clerk is offline   Reply With Quote
Old 08-03-2014, 08:09 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,168
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Multi criteria combo query problem

Would this be what you are after?

http://msaccesshintsandtips.ning.com...-box-to-return
__________________
Code:
                 |||||
               @(~Ô^Ô~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 08-03-2014, 10:47 AM   #3
compton clerk
Newly Registered User
 
Join Date: Aug 2014
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
compton clerk is on a distinguished road
Re: Multi criteria combo query problem

Thanks Uncle Gizmo.

Lots of info at the link. I will have a good look and see if it can be adapted to produce what I need. It looks hopeful.

compton clerk is offline   Reply With Quote
Old 08-03-2014, 11:04 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Multi criteria combo query problem

Quote:
Originally Posted by Uncle Gizmo View Post
This one has got the login problem Uncle T.
vbaInet is offline   Reply With Quote
Old 08-03-2014, 11:41 AM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,168
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Multi criteria combo query problem

Quote:
Originally Posted by vbaInet View Post
This one has got the login problem Uncle T.

I made the site Private again....

I have 4000 plus members and growing at about 2 or 3 a week... Sometimes 2 or 3 a day!

It's my retirement plan, but I haven't worked out how to make any money from it yet. (Seeing a Guy tomorrow)
__________________
Code:
                 |||||
               @(~Ô^Ô~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 08-03-2014, 11:55 AM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Multi criteria combo query problem

@Uncle T: I didn't know it was the entire content that got locked down, I thought you could unlock individual threads, but it makes sense now why it keeps doing that.

vbaInet 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
Multi Criteria DLookup syntax problem. jonnyboy101 Modules & VBA 6 12-04-2013 02:01 PM
Ignoring null criteria in a multi-criteria query… Nano Queries 16 10-29-2013 12:01 PM
multi field search criteria problem itchy Queries 6 07-26-2007 05:21 AM
Multi-Select ListBoxes as Report/Query Criteria, Using a Combo Box to select a Range Tad71 Forms 1 09-24-2003 11:14 PM
[SOLVED] using multi-select on a listbox to set query criteria criteria kom73 Forms 1 10-10-2001 03:45 AM




All times are GMT -8. The time now is 05:52 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