Link to home
Start Free TrialLog in
Avatar of Scamquist
ScamquistFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scamquist

ASKER

thank you for the assist/