[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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!combo2="show all",[gender],forms!mySearchForm!combo2) -- 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!
0
jpomerantz
Asked:
jpomerantz
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This question has a couple of approaches.  Let me know if they work for you.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27828682.html
0
 
jpomerantzAuthor Commented:
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!
0
 
russell12Commented:
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.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
jpomerantzAuthor Commented:
ok - amazing!!! here it is...
sample-db-multiple-parameter-sor.mdb
0
 
als315Commented:
Try this sample. Search in sort1...sort4 fields. You can enter any part of text in a field
sample-db-multiple-parameter-sor.mdb
0
 
jpomerantzAuthor Commented:
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!
0
 
als315Commented:
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.
0
 
als315Commented:
I can't undersatnd also why are you storing text in sort fields instead of ID?
0
 
als315Commented:
0
 
jpomerantzAuthor Commented:
thank you SO much! - this is exactly what I needed :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now