filtering for different field names

[prmitchell]

I have a user who wishes to be able to choose the field to search by and then enter/select a value for the criteria
for exaplanation
basically a customers table (but will also show information from orders in the output)
where the user can choose/specfiy suburb and then choose/enter a value eg. Bankstown
and/or
can choose/specify [marital status] and then choose/enter a value eg. S (single)
and/or
can choose/specify gender and then choose/enter a value eg. F (female)

so in many ways, they want to be able to (almost) write their own query

my thought is to have a combo box which includes the field names and then a text box where they can type in
and to have this pairing multiple times as they might want a list of females who live in Bankstown.

I have read something kind of similar in one of the threads about
Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*" on multiple OR lines

but I am not sure if/how that applies to my request.

I look forward to any help please.

 

[Brianwarnock]

Yes you can create a search form with multiple combo boxes which are populated from your table(s) then the user merely selects a value from the combo(s) that he wishes to filter on.
A command button then runs the query, the query has a series of ANDED not Orrd criteria, do not use Like unless you require partial searches.

The criteria looks like

Where
(Fldsuberb = forms!serachform!cbosuberb or forms!searchform!cbosuberb is null)
And
(fldgender = etc

If a combo is not selected the is null ensures that it is not involved in the filtering

Brian

 

[mikejaytlabustro]

http://www.youtube.com/watch?v=CTiA_4Me0cI

Hope this would help. Thanks to Mr. Richard Rost 599CD

 

[mikejaytlabustro]

http://www.youtube.com/watch?v=CTiA_4Me0cI

Hope this would help. Thanks to Mr. Richard Rost 599CD

 

[mikejaytlabustro]

Yes you can create a search form with multiple combo boxes which are populated from your table(s) then the user merely selects a value from the combo(s) that he wishes to filter on.
A command button then runs the query, the query has a series of ANDED not Orrd criteria, do not use Like unless you require partial searches.

The criteria looks like

Where
(Fldsuberb = forms!serachform!cbosuberb or forms!searchform!cbosuberb is null)
And
(fldgender = etc

If a combo is not selected the is null ensures that it is not involved in the filtering

Brian

Is it possible to populate the Field Names in the Combo Box, then create
another Text Box to write the search criteria?

ComboBox: [Where you can Select a Field]
TextBox: [Where you can Input criteria]
CommandButton: [To run the Query]

 

[Brianwarnock]

Is it possible to populate the Field Names in the Combo Box, then create
another Text Box to write the search criteria?

ComboBox: [Where you can Select a Field]
TextBox: [Where you can Input criteria]
CommandButton: [To run the Query]

Why would you want to do that?
The combo box is based on a field in the table and populated with all the values available , combined with the query and criteria merely selecting a value from the relevant combo achieves everything in one click, or maybe two.

Brian

 

[Brianwarnock]

A quick search returned this

http://www.access-programmers.co.uk/forums/showthread.php?t=183254

The explanation is ok but I have not looked at the sample DB however I have no reason to doubt it.

Brian

 

[prmitchell]

Thankyou Brian and Mike. My form has seven text boxes on it - and is kind of working. Having performed a search, for males for example then I clear the form and even manually refresh and then search for married a reduced resultant list is produced as I am getting married males. It's a little hard to check accurately because of having seven text boxes involved.

When the form activates should I set each text box to ""

or any other ideas most welcome please.

 

[Brianwarnock]

This post suggests that you did not read/understand the thread I suggested a s in there you will see how to do both searches in one pass.

Brian