Access 10 sql query

Question

I want to use LIKE operator in access 10 sql query with variable.

Example:

temporary variable var contains value bs
var = "bs"

I want to match every String that starts with value of temporary variable followed by zero or more numbers.

I am trying to fire the query:

select * from xyz where variety LIKE "@[tempvars]![var] + [0-9]*"

It is returning 0 records. Thankz for the help.

Answer 1

You need to refer to your tempvar outside of the quotes, and use & for concatenation:

select * from xyz where variety LIKE "@" & [tempvars]![var] & "[0-9]*"

This will return all records where variety starts with a literal @, then whatever is in [tempvars]![var], then a number, and then any amount of characters.