filtering to match any part of a field

[Paul James]

I'm using the following expression to filter records in a subform based on a
designated number of characters at the left of a field in the main form:

matchLast = "left(TMA_LastName, txtNumCharacters) = left((LastName),
txtNumCharacters)"

This filter displays records in the subform where the first
[txtNumCharacters] characters in the last name field match those of the
corresponding field in the main form.

The filter works fine, but I would prefer not to limit it to the leftmost
characters in the subform. I'd like it to select all records in the subform
that contain the matched string anywhere in the LastName field. For
example, if the LastName field in the main form is "Simpson," and
txtNumCharacters is 3, I'd like to display all records in the subform where
the characters "sim" appear anywhere within the LastName field. (I'm not
using a case-sensitive search).

Can anyone tell me how to modify my filter to accomplish this?

Thanks in advance.

 

[Marshall Barton]

I'm using the following expression to filter records in a subform based on a
designated number of characters at the left of a field in the main form:

matchLast = "left(TMA_LastName, txtNumCharacters) = left((LastName),
txtNumCharacters)"

This filter displays records in the subform where the first
[txtNumCharacters] characters in the last name field match those of the
corresponding field in the main form.

The filter works fine, but I would prefer not to limit it to the leftmost
characters in the subform. I'd like it to select all records in the subform
that contain the matched string anywhere in the LastName field. For
example, if the LastName field in the main form is "Simpson," and
txtNumCharacters is 3, I'd like to display all records in the subform where
the characters "sim" appear anywhere within the LastName field. (I'm not
using a case-sensitive search).

You can use the Like operator for partial matches using
wildcard characters.

matchLast = "LastName Like "*" & Left(TMA_LastName,
txtNumCharacters) & "*"

 

[Paul James]

Great. Thanks, Marsh.