Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Explicitly passing a .value
Hi,
I'm trying to filter by making an SQL statement which ties in the value of a cell. The cell in question is B15. In my particular test case, it has a value of 111-K. However, it will normally be a dynamic value in the same format. If I refer to it like I do in my code below, I receive the error "incorrect syntax near 'K' " If I change the query and say "AND MAIN.PART_NO = '111-K' " it works fine, because its tied together with the quotes. Any thoughts as to how I can get this working based on a cell value? The code is below strQuery3 = "SELECT PARTS.TYPE " & _ "FROM MAIN, PARTS " & _ "WHERE MAIN.ID=INDUCTORS.PART_ID " & _ "AND MAIN.PART_NO =" & Range("B15").Value With rs3 .Open strQuery3, dbConn2, adOpenStatic noRecords = .RecordCount Range("D15").CopyFromRecordset rs3 .Close End With Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Explicitly passing a .value
Try wrapping the value of B15 in single quotes:
"AND MAIN.PART_NO ='" & Range("B15").Value & "'" mazzarin wrote: Hi, I'm trying to filter by making an SQL statement which ties in the value of a cell. The cell in question is B15. In my particular test case, it has a value of 111-K. However, it will normally be a dynamic value in the same format. If I refer to it like I do in my code below, I receive the error "incorrect syntax near 'K' " If I change the query and say "AND MAIN.PART_NO = '111-K' " it works fine, because its tied together with the quotes. Any thoughts as to how I can get this working based on a cell value? The code is below strQuery3 = "SELECT PARTS.TYPE " & _ "FROM MAIN, PARTS " & _ "WHERE MAIN.ID=INDUCTORS.PART_ID " & _ "AND MAIN.PART_NO =" & Range("B15").Value With rs3 .Open strQuery3, dbConn2, adOpenStatic noRecords = .RecordCount Range("D15").CopyFromRecordset rs3 .Close End With Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Explicitly passing a .value
Bingo!
Figured I had to manipulate the single quotes some special way, couldn't figure out how. Thanks again. Bill Schanks wrote: Try wrapping the value of B15 in single quotes: "AND MAIN.PART_NO ='" & Range("B15").Value & "'" mazzarin wrote: Hi, I'm trying to filter by making an SQL statement which ties in the value of a cell. The cell in question is B15. In my particular test case, it has a value of 111-K. However, it will normally be a dynamic value in the same format. If I refer to it like I do in my code below, I receive the error "incorrect syntax near 'K' " If I change the query and say "AND MAIN.PART_NO = '111-K' " it works fine, because its tied together with the quotes. Any thoughts as to how I can get this working based on a cell value? The code is below strQuery3 = "SELECT PARTS.TYPE " & _ "FROM MAIN, PARTS " & _ "WHERE MAIN.ID=INDUCTORS.PART_ID " & _ "AND MAIN.PART_NO =" & Range("B15").Value With rs3 .Open strQuery3, dbConn2, adOpenStatic noRecords = .RecordCount Range("D15").CopyFromRecordset rs3 .Close End With Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing libraries explicitly in macro code | Excel Programming | |||
How to explicitly set the regional code of an individual spreadsheet | Excel Discussion (Misc queries) | |||
Solver with values not stored explicitly in cells | Excel Programming | |||
Passing a Range | Excel Programming | |||
passing defined name to vb | Excel Programming |