Search Criteria Form

So I've managed to get my criteria form up and going in access, and it searchs Name/Cit开发者_JAVA技巧y , but it only searches for the EXACT city name, even though ive given it a wildcard, and the name field works as a search any part of the field? Anyone know why? here is my code:

 'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([City] Like ""*" & Me.txtFilterCity & "*"") AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If

Thanks!

Here is a different approach to building strWhere.

' Text field example. Use quotes around the value in the string. '
If Not IsNull(Me.txtFilterCity) Then
    strWhere = strWhere & " AND City Like ""*" & Me.txtFilterCity & "*"""
End If
' Another text field example. Use Like to find anywhere in the field. '
If Not IsNull(Me.txtFilterMainName) Then
    strWhere = strWhere & " AND MainName Like ""*" & Me.txtFilterMainName & "*"""
End If
' chop off leading AND '
strWhere = Mid(strWhere, 6)
Debug.Print strWhere

I discarded the parentheses and square brackets because they weren't needed here. Square brackets surrounding a field name are needed if the field name includes spaces, punctuation characters, etc. Square brackets are also useful if the field name is a reserved word. I prefer to use only characters which don't require bracketing in object names, and avoid reserved words as object names.

if you are using access then

* allows you to match any string of any length (including zero length)

? allows you to match on a single character

# allows you to match on a single numeric digit

eg:

Like 'b*' would return all values that start with b

Like '*b*' would return all values that contain b

Like '*b' would return all values that end with b

Like 'b?' would return all values that start with b and are 2 characters in length

Like 'b#' would return all values that start with b and are 2 characters in length where the second character is a number