passing *everything* criteria to query from form

[bobt9901]

Hi All

I am trying to pass variables to a query from a form. I have some drop downs to select the relevant criteria. I put the name of the control on the form into the criteria of the query. This works fine to a point.

I cannot however give a wildcard. e.g. i can select from a 'level' drop down either level 1,level2,level3 but cannot select 'all'

I would like to be able to either allow multiple values in the drop downs to add more criteria to the search query or add a 'all' to the combo


Any ideas?

[R_Badger]

You best bet here is to 'assemble' the SQL you need as you need it ( I am making a slight assumption that this query feeds the same form..)

[June7]

Why not wildcard the parameter? If the combobox is left blank, that will equate to 'all'.

The alternative is as R_Badger suggests. Use VBA code to assemble the filter string. Is the query used as the RecordSource for a report (or form)? Use the assembled string in the WHERE CONDITION argument of DoCmd.OpenReport (or OpenForm). If it is for the same form holding the search combos then use the string to set Filter property and set FilterOn property as needed to turn filter on and off.

[bobt9901]

hi

i tried leaving the combo box blank but it did not seem to remove that criteria, it did not return anything.

[R_Badger]

Having slept on it, I had a thought.

can you post the SQL from your query? If my thoughts are correct I should be able to help you.

[Subwind]

I have used this code in alot of my query filters.

I then have an "Apply Filter" button which requeries the list box based on the filters.

If left blank, this shows all records.

Is this the sort of thing you are looking for?

~Matt

[June7]

Subwind, your code does not display in post. Is it an image you tried to attach?

[Subwind]

Subwind, your code does not display in post. Is it an image you tried to attach?

It was an image, dropbox link. Does it show now?

~Matt

[June7]

Yes, it now shows.

[R_Badger]

another option to try it its only one or two boxes you use like that is:

like "*" & [forms]![frmcustomerlist]![txtcustomerfilter] & "*" or [forms]![frmcustomerlist]![txtcustomerfilter] is null.

If however you plan on using this technique on more than one or two controls/fields then you are better off assembling the SQL on the form. I have a printout somewhere of the SQL of a query which had 6 controls like that... it ended up being 15 a4 pages long, the SQL needed for the most complex where criteria is less than 2 lines long if it is assembled on demand.