VBA escape character

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello. I am trying to build an SQL statement in access.
I want it to substitute a value typed into a text box
into the SQL statement. Part of my code reads:

strqry = strqry & "((ACCOUNT_LIST.COMPLEMENTOR)=" &
txtCOMP & ") AND "

all on one line of course.

txtCOMP is the value that I want to show up in the actual
SQL code. Lets say that txtCOMP = asdf. I want the
actuall SQL code to read:

((ACCOUNT_LIST.COMPLEMENTOR)="asdf") AND

My problem is that I am not sure how to get the qoutes
around asdf to show up in the SQL code. I have no
problem getting it not to show up, though:

((ACCOUNT_LIST.COMPLEMENTOR)=asdf) AND

But as far as I know, I need the quotes for the statement
to work correctly.

Is there an escape character or something that allows me
to use the quotes without the VBA complier complaining
about a syntax error?

Because this:
strqry = strqry & "((ACCOUNT_LIST.COMPLEMENTOR)="" &
txtCOMP & "") AND "
does not work.

Thanks for any help. If I need to clarify anything,
please let me know.
 
Try doubling them. "" will turn into " when it goes into
your String.

-Cameron Sutherland
 
Brian said:
Hello. I am trying to build an SQL statement in access.
I want it to substitute a value typed into a text box
into the SQL statement. Part of my code reads:

strqry = strqry & "((ACCOUNT_LIST.COMPLEMENTOR)=" &
txtCOMP & ") AND "

all on one line of course.

txtCOMP is the value that I want to show up in the actual
SQL code. Lets say that txtCOMP = asdf. I want the
actuall SQL code to read:

((ACCOUNT_LIST.COMPLEMENTOR)="asdf") AND

My problem is that I am not sure how to get the qoutes
around asdf to show up in the SQL code. I have no
problem getting it not to show up, though:

((ACCOUNT_LIST.COMPLEMENTOR)=asdf) AND

But as far as I know, I need the quotes for the statement
to work correctly.

Is there an escape character or something that allows me
to use the quotes without the VBA complier complaining
about a syntax error?

Because this:
strqry = strqry & "((ACCOUNT_LIST.COMPLEMENTOR)="" &
txtCOMP & "") AND "
does not work.
Click to expand...


Ther are several ways to accomplish this. A key rule is
that you can use a qouble quote to escape another double
quote:

strqry = strqry & " (ACCOUNT_LIST.COMPLEMENTOR = """ _
& txtCOMP & """) AND "

An alternative is that SQL also accepts the single quote as
well as the double quote:

strqry = strqry & " (ACCOUNT_LIST.COMPLEMENTOR ='" _
& txtCOMP & "') AND "

If you find all those quotes confusing, you may prefer to
use the CHr function to place the quote in the expression:

strqry = strqry & " (ACCOUNT_LIST.COMPLEMENTOR = " _
& Chr(34) & txtCOMP &Chr(34) & ") AND "

Finally, it the value of txtCOMP might contain the quote
character you chose to use around txtCOMP in the expression,
then you'll need to double up on that quote:

strqry = strqry & " (ACCOUNT_LIST.COMPLEMENTOR = """ _
& Replace( txtCOMP, """", """""") & """) AND "
 
Back
Top