Joel Pomerantz
asked on
designing multiple parameter searchable form
Hello, I'm using access 2003 and am trying to create a search function that would allow me to do the following:
i have a table with 4 fields called "sort1" "sort2" "sort3" and "sort4"
on the form i have 4 combo boxes that pull from the same menu of sort options (in a seperate table); for some clients I fill in one field, for others, two and so on...
I need to be able to write a query that would pull up any record that, for ex, said "lawyer" in ANY of the 4 fields -- how can I do that???
I'd also like to be able to narrow by more than one attribute, for ex - to bring up records that are "lawyer" AND "personal" - again, in any of the 4... (i also have another combo box listing "male" or "female" and I'd like to also qualify the search by gender or "show all" etc...)
i did the gender one already by setting up a query that reads iif(forms!mySearchForm!com bo2="show all",[gender],forms!mySear chForm!com bo2) -- this works fine for gender - but i'm stuck on the others cuz of the complexity of needing it to pull from ANY of the fields... i hope i'm expressing this clearly enough :) let me know what else you need to know
thanks so much!
i have a table with 4 fields called "sort1" "sort2" "sort3" and "sort4"
on the form i have 4 combo boxes that pull from the same menu of sort options (in a seperate table); for some clients I fill in one field, for others, two and so on...
I need to be able to write a query that would pull up any record that, for ex, said "lawyer" in ANY of the 4 fields -- how can I do that???
I'd also like to be able to narrow by more than one attribute, for ex - to bring up records that are "lawyer" AND "personal" - again, in any of the 4... (i also have another combo box listing "male" or "female" and I'd like to also qualify the search by gender or "show all" etc...)
i did the gender one already by setting up a query that reads iif(forms!mySearchForm!com
thanks so much!
ASKER
looks pretty similar to what I'm trying to do... will have to play around with it; silly question: I can't seem to open the same dbs in that thread - could it be bec i have access 2003 and not 2007?? shouldn't it still be compatible? what should I do? (don't think I'll be able to figure it out with the code posts alone - need to see sample and try to backwards analyze it...
thanks so so much!
thanks so so much!
Sounds fimiliar to what I just did. If you could post a sample db of what ur trying to achieve, I will then try to write it for you and describe what all I did.
ASKER
ok - amazing!!! here it is...
sample-db-multiple-parameter-sor.mdb
sample-db-multiple-parameter-sor.mdb
Try this sample. Search in sort1...sort4 fields. You can enter any part of text in a field
sample-db-multiple-parameter-sor.mdb
sample-db-multiple-parameter-sor.mdb
ASKER
oops - i don't think i was clear enough about what I'm trying to do... i'm trying to get these combo boxes on the "contactsSearch" form to work - the "search" box on the MasterContactsForm is just to find individual records...
sorry for the confusion!!
is what i'm trying to do possible?? to have data in all the 4 different fields - and when I choose that iten in one of the combo boxes on the "contactsSearch" form - it would pull all records with that item in any of the four - and then let me use the other combos to further narrow the parameters...
thanks so so much for your help!
sorry for the confusion!!
is what i'm trying to do possible?? to have data in all the 4 different fields - and when I choose that iten in one of the combo boxes on the "contactsSearch" form - it would pull all records with that item in any of the four - and then let me use the other combos to further narrow the parameters...
thanks so so much for your help!
May be better to change DB structure? You can add table where will be Contact ID and 1 sort field. For 1-st contact you will have 2 records, for 3-rd only one :
ContactID Sort
1 CBT
1 consultant
3 nuerologist
In this case you can select all possible sorts in simple query and filter will contain only 4 conditions. With current structure you should have filter with 4*4=16 conditions.
ContactID Sort
1 CBT
1 consultant
3 nuerologist
In this case you can select all possible sorts in simple query and filter will contain only 4 conditions. With current structure you should have filter with 4*4=16 conditions.
I can't undersatnd also why are you storing text in sort fields instead of ID?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you SO much! - this is exactly what I needed :)
https://www.experts-exchange.com/questions/27828682/How-to-create-compund-filters-on-a-form.html