Using Like when field contains a #

[Jim Tinder]

I have a form with a field to enter a parameter to use to
list the contents of a table in a subform. For example an
address field may contain:
1. Null
2. 19 Elm Street
3. #24 Elm St.
4. Elm Street Apartments.
5. 49 State St.

If the parameter field on the form is blank, all records
should be shown. If a parameter of "Elm" is entered;
records 2, 3, and 4 should be shown. I have resolved
the "null" condition in a first level query. A second
query is built over the first to test the criteria entered
on the form. This works OK for all but the situation
except when the field in the record contains a (#)pound
sign.

The criteria in the query reads:

Like [forms]![myform]![my_parameter] & "*"

Record 3 above is excluded in the record set because
Access sees the "#" as a wildcard.

The knowledge base list a solution for finding a wildcard
in a field ("[#]") but nothing on how to have access
ignore the character as a wild card.

Any ideas....
Thanks
Jim T.

 

[Ken Snell]

You need to surround the # character with [ ] characters in the string that
you get from the form. I use a function similar to this to convert the text
string:


Public Function PoundAddBrackets(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION SURROUNDS ONE "#" CHARACTER WITH [ AND ] CHARACTERS
'***IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted
On Error GoTo Err_PoundAddBrackets
PoundAddBrackets = Replace(Nz(xstrReplaceStringValue, ""), "#", "[#]",
1, -1, vbTextCompare)
Exit Function

Err_PoundAddBrackets:
PoundAddBrackets = xstrReplaceStringValue
Resume Next
End Function