Using variables in a filter string

Question

I have a subform that returns data from a table. However I want to further filter this data by using the filter property.

I have a group of radio buttons, combo boxes and a case statement which sets the variables I want to use to the correct values depending on the radio button selection

My code for filling the variables works perfectly but I cannot use vba to set the filter unless I manually type the string that I want.

I assume that my issue is that my filter string is syntactically incorrect but I am unsure of how. Probably something to do with text delimiters.

Forms![frmPendingActions]![qryPendingAction subform].Form.Filter = Filterby = FilterCrit
Forms![frmPendingActions]![qryPendingAction subform].Form.FilterOn = True

Assume for this question that Filterby=[Reporter] and FilterCrit= Fake Name

Mark3308 · Accepted Answer · 2012-06-18 16:31:42Z

2

Yes I think the problem is to do with text delimiters. The code should look similar to the following:

Forms![frmPendingActions]![qryPendingAction subform].Form.Filter = "[Reporter] ='" &  FilterCrit  & "'"

The filter should be built exactly the same as a where clause without the word where. If the column you are filtering by has a text datatype then the criteria needs to be enclosed in single or double quotes. If the column is a date datatype then it needs to be enclosed in #. If the column is a number datatype then it does not need to be enclosed.

If you do not always wish to filter by the Reporter column then you can build a string using if statements or select case statements and then apply that string to the form filter.

For example:

If [somecondition] Then
   strFilter="[Reporter]='" & FilterCrit & "'"
Else
   strFilter="[ID]=" & 0
End If
Forms![frmPendingActions]![qryPendingAction subform].Form.Filter = strFilter

I hope this helps.

edited Jun 18, 2012 at 16:31

answered Jun 18, 2012 at 8:42

Mark3308

1,36111 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VBwhatnow Over a year ago

Thank you, this is promising. The filter appears to work correctly but I think you may have misunderstood my question a little. The column that I want to filter by is also a variable, how would I handle that?

VBwhatnow Over a year ago

The column is a text datatype by the way

VBwhatnow Over a year ago

Nevermind, I adapted the rest of my code around you statement.

Collectives™ on Stack Overflow

Using variables in a filter string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related