how to escape the ' in VBA

[RompStar]

I know that this character is used for comments in VBA: '

but I have a need to use it in my code, here is some simple code to simulate my situation and what I am trying to accomplish. Basically I am working with the Oracle driver to connect to the database, execute a series of different queries and then have the data be pasted back in Excel for final analysis.

When I execute this code, I get this:

SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION=10_A;

but I need this:

SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION='10_A';

because the VERSION column is a VARCHAR2 field and I need the tickmarks around the value to select. How do I employ the ' without having everything turn into comments ?



Sub Macro1()

iVersion = "10_A"

Sql_Main_Table = "SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION=" & iVersion & ";"

Sheets("Sheet1").Range("A1").Value = Sql_Main_Table

End Sub

 

[cstimart]

I believe you would just use CHR(39) in the place of the '.

 

[Scott Huish]

How about:

Sql_Main_Table = "SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION='" & iVersion & "';"

 

[RompStar]

Cool, thanks!

 

[p45cal]

or this:

Sub Macro1()
iVersion = "'10_A'"
Sql_Main_Table = "SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION=" & iVersion & ";"
Sheets("Sheet1").Range("A1").Value = Sql_Main_Table
End Sub

 

[ChrisM]

You'd use the Char() function. Sorry, don't remember the number for ', but once you find it, your statement would look like this:

dim sql as string
sql = "SELECT COLUMN1, COLUMN2 FROM TABLE WHERE VERSION=" & Chr(10) & "10_A" & Chr(10) & ";"

 

[RompStar]

thanks guys!

 

[RompStar]

I was getting error run-time 440, for some strange reason, it don't like the ; at the end,
but in SQL world that is used to end the Query, weird, I removed it and it works now :- )

Thanks!