Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query: Search Criteria "Similar to" Cell Reference
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query: Search Criteria "Similar to" Cell Reference
Dim SQL as string
SQL = "select * from tablename t where t.columnname like '" & _ Left(activesheet.range("B1").value, 4) & "*' " Tim "ToddEZ" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"search" a different sheet with multiple criteria | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How to write parameter query with "or is null" criteria | New Users to Excel | |||
Excel VBA - go to worksheet "name" based on a cell reference ="Name" | Excel Programming | |||
How do I show Auto-Filter "Search" Criteria on Sheet1? | Excel Programming |