I am pasing a SQL statement from Access to SQL Server 1

[DougP]

It is an INSERT INTO

here is th epart generating the Error

VALUES ('009599', '5UPS/GROUND', 'NEEDHAM'S', 'ATTN: WILLIE NEEDHAM 1-610-869-9377',

As you can see each value is separated by a single quote
But the company name NEEDHAM'S in this case has an Apostrophe in it.
The Error gerated is "Missing Operator"

Does anyone know of a work around for this ??
I tried adding Chr$(34) & Chr$(39) etc but got the same Error, casue the final result when Debug printed out is the exact same.
99% of the records work just fine, its just this one.
Appareantly deleting the Apostrophe from that One Company name is NOT an option.

TIA
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Andel]

Can you put double quote in NEEDHAM'S? Like

VALUES ('009599', '5UPS/GROUND', 'NEEDHAM''S', 'ATTN: WILLIE NEEDHAM 1-610-869-9377')


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[petermeachem]

Could you post the exact code you used with chr$(34). That always works for me. Peter Meachem
peter@accuflight.com

 

[Bernadette]

Hi There

Like Andel says. To insert an apostrophe in a sql statement, you need to put in 2 apostrophes.


VALUES ('009599', '5UPS/GROUND', 'NEEDHAM''S', 'ATTN: WILLIE NEEDHAM 1-610-869-9377')
Hope This Helps
Bernadette

 

[DougP]

Thanks guys the work around I have is:
Looking for the Offending apostrophe and replacing it with a blank space.
Believe it or not I thought the Error message had the exact location of the offending apostrophe. Which you can see from my post above
I then narrow it down to just the word itself. Then I fix it with a space then I do a replace and put back the fixed words into the original string.
Wild or What. Since this is a shipping label a space is OK

here is my VBA code to do that
-------------------------- Case 3075 is part of my Error routine
Case 3075
' Syntax Error missing operator (probably becasue there is an apsotrophe in one of the fields)
WhereAphs = InStr(15, Err.DESCRIPTION, "expression &quot
FindComma = InStr(WhereAphs, Err.DESCRIPTION, ",&quot
BadData = Mid(Err.DESCRIPTION, 42, FindComma - WhereAphs)
RightSide = Right(BadData, Len(BadData) - 13)
LeftSide = Left(RightSide, Len(RightSide) - 1)
LeftSideFixed = LeftSide
FindApsotrophe = InStr(1, LeftSide, "'&quot
Mid(LeftSideFixed, FindApsotrophe, 1) = " "
'= Left(Right(BadData, Len(BadData) - 1), Len(BadData) - 1)
StartPosition = InStr(1, SQL, LeftSide)
Mid(SQL, StartPosition, Len(LeftSideFixed)) = LeftSideFixed
Resume FixApostophe ' this returns back to right above where it passes the SQL




DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive