Scamquist
asked on
Access VBA - Search field for string where string does not have number immediately before or after
I have some VBA code in Access to look in a string field and return records that contain another string.
For example:
SearchField may be:
BB13118000022,SUPPORT PROFILE TURN CAP
and I want to return the record if the string (strfind) is 3118000022
The statement below returns the proper records
strcriteria = "SearchField LIKE '" & "*" & strfind & "*" & "'"
However, if the character immediately before or after strfind is a number, I do not want to return that record.
I tried
'remove records with number before strfind
strcriteria = "SearchField NOT LIKE '" & # & "*" & strfind & "*" & "'"
'remove remainin records with number after strfind
strcriteria = "strcriteria NOT LIKE '" & "*" & strfind & "*" & # & "'"
'remaining records, return with strfind
strcriteria = "strcriteria LIKE '" & "*" & strfind & "*" & "'"
it failed on the # symbol
For example:
SearchField may be:
BB13118000022,SUPPORT PROFILE TURN CAP
and I want to return the record if the string (strfind) is 3118000022
The statement below returns the proper records
strcriteria = "SearchField LIKE '" & "*" & strfind & "*" & "'"
However, if the character immediately before or after strfind is a number, I do not want to return that record.
I tried
'remove records with number before strfind
strcriteria = "SearchField NOT LIKE '" & # & "*" & strfind & "*" & "'"
'remove remainin records with number after strfind
strcriteria = "strcriteria NOT LIKE '" & "*" & strfind & "*" & # & "'"
'remaining records, return with strfind
strcriteria = "strcriteria LIKE '" & "*" & strfind & "*" & "'"
it failed on the # symbol
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER