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!

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!

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!