Microsoft Access - Simulate Fuzzy Searches With The Filter() Function (Access 2000/2002/2003)

You're no doubt familiar with the Like keyword in SQL. It allows you to return multiple records based on a selection criteria string. Well, the VBA offers a function that behaves similarly to the Like statement, but for one-dimensional arrays. It's called the Filter() function. The syntax for the Filter() function is as follows:

  • objArray = Filter(InputStrings, Value
  • [, Include[, Compare]])

For the InputStrings argument, you'll supply a one-dimensional array of string values. The Value argument represents the string of characters you're looking for in the array. The optional Include argument allows you to specify whether the new array should consist of records that contain the Value or records that don--t contain the Value. The Compare argument is also optional. A value of 0 indicates you want binary comparison (case sensitive) and a value of 1 requests a text comparison (not case sensitive). Consider the following string array with a few example calls to the Filter() function:

  • Dim test as string
  • test = "Boat,Coat,Load,Float,Brick,Oat Bran"
  • Dim objArray as Variant
  • objArray = Split(test,",")
  • Debug.Print (Join(Filter(objArray, "oat", 1, 0), ","))
  • Debug.Print (Join(Filter(objArray, "oat", 1, 1), ","))
  • Debug.Print (Join(Filter(objArray, "oat", 0, 1), ","))

If you place this code in a function or subroutine in your module and execute it in debug mode, the resulting Immediate window would contain:

  • Boat,Coat,Float
  • Boat,Coat,Float,Oat Bran
  • Load,Brick

Go back