Access VBA - Search field for string where string does not have number immediately before or after

Scamquist used Ask the Experts™
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:


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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
You will need to use the IIf(), Instr(), Len(), and Mid() functions.

IIf(IsNumeric(Mid(yourstring, instr(yourstring) -1, 1), False, True)

That is pretty basic and finds a character to the left of the string.  However, it will fail if the big string starts with the string you are looking for so you need to check for that also.  To find the numeric character to the right is a little more complex.  The Instr() gives you the location of the beginning of the string so to find the character that follows it, you need to add the Len() of the search string to the instr() value and then add 1.  Again, if that brings you past the end of the string it will raise an error.

I would write a user function instead since the nesting of the VBA functions will be very difficult to read once you add in the error checking to make sure you don't go past the beginning or the end of the string.

Since the function will be used in the Where Clause, I would be leery of using it against linked ODBC tables.  In that case, I would use two queries in an attempt to convince Access to send part 1 (the inner query) to the server and then apply the UDF locally.  So the inner query would be as you have written it but the outer query would use the function to make the final selection.


thank you for the assist/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial