Various multiple criteria

[shaggyjb]

I am making a query for my database that could be used in many different ways. There are 9 fields being searched, but the search parameters are different everytime this query is used. I need some way to be able to choose what fields the search is based on, it could be based on the results of 1, 2 or even 3 fields.

i would appreciate the help if someone could point me in the right direction.

sorry if this doesnt make much sense, i am fairly new to access.

 

[Rob.Mills]

Tell me a little more. Is this query going to pull parameters from fields on a form? Comboboxes? I just want to get some more details so I can direct you better.

 

[shaggyjb]

Well, I would like to pull the parameters from fields on a form, I havent actually made this part of my db. But i need to be able to choose up to three different parameters every time the search is used and display the corresponding values for the same 9 fields found in some table. I really dont know how to go about it. i guess if a listbox would work better, i could go with that.

 

[Rob.Mills]

Actually a listbox might be more complicated in this situation. Here's what I would do.

Setup 3 (or as many as you need) comboboxes on the form. Set the recordsources of each combobox to selections you'll want as parameters.

Then in the criteria of each field put this:

iif(isnull([Forms]![FormName]![ComboboxName]),[Name of field in query],[Forms]![FormName]![ComboboxName])

What this does is if the combobox is null it will not limit anything in that field. If it does have a selection, limit the data by that selection.

 

[shaggyjb]

thanks for the help Rob. the query works good with the multiple selection, but everytime i run it with the chosen lookup values i write over an old record or add a new record to the table. ive tried messing with the settings but i can not get the query to stop writing over fields. if anyone else wants to help me out thats cool also.

 

[shaggyjb]

bump