Dynamic Filter with multiple possible filter criteria

[benkingery]

I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:

If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:

"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"



If I have values in only field 7, I'd want th efilter string to be created as follows:

"...WHERE field7 = field7filter.value"


And so on and so on.

I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.

Any ideas? Thanks in advance!

 

[pbaldy]

The sample db here does that kind of thing:

http://www.baldyweb.com/BuildSQL.htm

 

[benkingery]

Lots of information there, kind of tough to get through with all the specific syntax, but I gather this is probably how this will go. Most of the examples you gave on that tutorial deal with opening a recordset. If I can successfully get the record set to open by building a specific "WHERE" clause through all the syntax, how do i set the form's data source equal to the recordset that is produced? This is something I don't know about.

Would it be easier to build a query behind the form to provide data to the form? or would this not be possible?

Here's what I have so far, can you tell me if my approach is completely wrong, or if I just need to work with my syntax a little bit?

Filter = "field1 = """ & "" & Me.field1filter & "" & """ AND field2 = """ & "" & Me.field2filter & "" & """ AND field3 = """ & "" & Me.field3filter & "" & """ AND field4 = """ & "" & Me.field4filter & "" & """ AND field 5= """ & "" & Me.field5filter & "" & """ AND field6 = """ & "" & Me.field6filter & "" & """ AND field7 = """ & "" & Me.field7filter & "" & """ AND field8 = """ & "" & Me.field8filter & "" & """ AND field9 = """ & "" & Me.field9filter & "" & """ AND field10 = """ & "" & Me.field10filter & "" & """ AND field11 = """ & "" & Me.field11filter & "" & """ AND field12 = """ & "" & Me.field12filter & "" & """ "

 

[pbaldy]

If you look at the sample db, it sets the source of a subform. You could also leave the source alone and set the filter of an already open form, or create a wherecondition to open a new form.

To your code, I personally would only add to the string if a search field had been filled in, like your example in the first post. You appear to have included them all in this last post.

 

[benkingery]

Thanks pbaldy. Is there a way to do a big long static string where the filtered results will not be compromised by a non-entry into one of the fields? Does this question make sense?

Is it possible to list out all 12 fields as I've done, but if there is not value in a particular field, the results from the filter will not be compromised?

 

[pbaldy]

Possibly, but that seems like a different direction than what you originally asked for. You can use Like with wild cards, but it doesn't work well with numeric values, and probably not date values. You can use this type of thing:

http://access.mvps.org/access/queries/qry0001.htm