Include ampersand in a string variable

How can I include the ampersand character in a string variable?  I am using this variable in an INSERT INTO table statment and the string looks fine when sent to the Immediate window, however when the string is inserted into a table is is cutoff prior to the ampersand.

Thanks!!
silsubaAsked:
Who is Participating?
 
Shailesh15Commented:
Just a thought ....Check your field Length.
0
 
Steve BinkCommented:
My first thought is to change "&" to "&&".  Many apps use "&" to signify the beginning of a special character code.  For a more specific solution, please post your INSERT statement.
0
 
JohnK813Commented:
chr(38) is the ASCII code for &

So, you'd say strMystring & chr(38)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
silsubaAuthor Commented:
Here's the insert statment


INSERT INTO tblCompany (companyName,sectorID,marketID,submarketID,relationID,website,datestamp) VALUES ('" & strCompanyName & "','" & intSectorID & "'," & _
                "'" & intMarketID & "','" & intSubmarketID & "','" & intRelationID & "','" & strWeb & "','" & dteDate & "')

I assign this to the string variable mySQL, and when I print the string to the immediate window I get something like this...

INSERT INTO tblCompany (companyName,sectorID,marketID,submarketID,relationID,website,datestamp) VALUES ('University of Pennsylvania, School of Engineering & Applied Sciences','1','4','17','0','','4/16/2004')

Now when I execute mySQL everything is inserted into the table but the companyName stops prior to the "&", in the example above, University of Pennsylvania, School of Engineering is the only information inserted.  

I did try this using your suggestion JohnK

 mySQL = Replace(mySQL, "&", Chr(38))

but still did not insert correctly.

0
 
PlamodoCommented:
Just a thought.. instead of using single quotes around string values in your sql statement like this: '

.. Maybe use chr$(34) instead.

INSERT INTO tblCompany (companyName,sectorID,marketID,submarketID,relationID,website,datestamp) VALUES (chr$(34) & strCompanyName & chr$(34) & "','" & intSectorID & "','" & _
                "'" & intMarketID & "','" & intSubmarketID & "','" & intRelationID & "','" & strWeb & "','" & dteDate & "')
0
 
silsubaAuthor Commented:
I was looking in the wrong direction for the answer... thanks Shailesh15!!
0
 
JohnK813Commented:
Shailesh15, that was my next thought... guess I picked a bad time to go to lunch!

silsuba, glad your question was answered.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.