Conditional Query Critera

[Scottish_Anatomist]

Hi Everyone,

I've created a search form where you can view multiple columns of data and can narrow down the data by searching via text box. I did this with the following code:

Private Sub txt_MarketSearchBox_Change()

Dim SearchString As String

SearchString = txt_MarketSearchBox.Text
txt_MarketSearchText.Value = SearchString

Me.lst_MarketSearch.Requery

In the query for the list box the criteria applied to one of the fields is :

Like "*" & [Forms]![frm_ProgrammeSearch]![txt_MarketSearchText] & "*"

However, what i'd like to do is to be able to search by different fields (columns) by selecting the desired one in a combo box next to the search text box. So for example, if the listbox displays the company name, product and sales code, the combobox would have each of these as an option and then the text entered into the textbox would be applied as a criteria to that field in the query.

I originally tried to apply an IF statement to the criteria to check the value of the combobox, however then found out you can't use the Iif in that part of the query.

Any help would be greatly appreciated

 

[Minty]

You can't apply criteria to a dynamic field in a fixed query. You would have to build your query based on the some VBA either by using a query def or applying a filter to the form using VBA. Also this doesn't make much sense.

SearchString = txt_MarketSearchBox.Text 
txt_MarketSearchText.Value = SearchString

Me.txt_MarketSearchBox is the same as txt_MarketSearchText.Value
Me.txt_MarketSearchBox.Text is simply the actual text string in the box. You wouldn't normally need to refer to this.

If you made a combobox (call it cmbFilter) with your field listings as the record source, you could then use some VBA to do the following in the after update of the combo;

Me.Filter = "[" &  me.cmbFilter & "] = '*" & me.txt_MarketSearchBox & "*' "
Me.FilterOn = True

 

[Scottish_Anatomist]

Thanks for the advice Minty, I really appreciate it!

 

[jdraw]

There is code in this sample database that will allow you to search across multiple fields in multiple tables. Don't know your real requirements, but this link may be useful.

Good luck.