Search code examples
ms-access

Compound pattern construction in WHERE clause of Query?

Right now I have a query that populates a listbox based on an unbound field's contents when the user hits the 'search' button.

FROM Constituents
WHERE Constituents.LastNm LIKE [Forms]![F_MainInterface]![LastNameSearchBOX];

This works. But the user has to know/put in all of the wildcards which testing has shown is not intuitive for the users this is for.

I'd like to have the query automatically supply * at the beginning and end of the string entered by the user. (e.g. if the user enters 'rak' the query will end with 'LIKE rak'

For the life of me I can't find the syntax to app/pre-pend anything to a dropped in field. How would I add the *'s to the query string?

Solution

  • MsAccess SQL can use & as the concatenate operator:

    WHERE 
 Constituents.LastNm LIKE '*' & [Forms]![F_MainInterface]![LastNameSearchBOX] & '*'
``
You could also use the + operator to concatenate strings but this would result in error if the box is empty (NULL), then the LIKE expression evaluates to NULL and you may get invalid use of NULL value (or similar).