MS Query: Search Criteria "Similar to" Cell Reference

[Guest]

Does anyone know if it is possible to perform a query based on the begining
few letters of a word in a cell reference?

Background: I have 2 databases with client identification numbers. In the
first (my excel workbook), they look like "12345", in the second databse (ms
access), they begin with the same few digits, but may vary after the first 4
characters (like "1234567-R" or "1234--P"). Currently, my query runs off a
cell reference to the 'ms access formated client id' which has to be
mannually entered into the worksheet after you look it up in access.
Unfortunately, the id's can not be changed in either database, and I really
dont want to harcode an index due to the constant addition/subtraction of
id's.

I would like to just use a reference to the 'excel formated client id' to
perform the access query, but because the id's dont match exactly in both
databases, it will not work. Is there a way to use the criteria (such as
"like") when I am using a cell reference to perform a query? For example i
would like my criteria to be the first 4 characters of cell b1, with the
remainder characters wildcards ...Like '=(left, b1, 4)%'???

I've had no luck thus far... let me know if any one has a work around.

 

[Tim Williams]

Dim SQL as string
SQL = "select * from tablename t where t.columnname like '" & _
Left(activesheet.range("B1").value, 4) & "*' "


Tim