Quote Marks ( AGHHH!)

  • Thread starter Thread starter APH
  • Start date Start date
A

APH

Hi I am using the following code to update a record:

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & LName & "'WHERE [SWPurchaseId]=" & SWID

LName is a public variable and contains a text string. occassionally
however, the text string itself contains an apostrophe (eg St Gabriel's)and
when it does I am getting a syntax error.

Other than going through 2500 records removing the apostrophies, can anyone
suggest a way round the issue. I guess I need some more quote marks in my
syntax, but not sure where

thanks

Alex
 
APH said:
Hi I am using the following code to update a record:

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & LName & "'WHERE [SWPurchaseId]=" & SWID

LName is a public variable and contains a text string. occassionally
however, the text string itself contains an apostrophe (eg St Gabriel's)and
when it does I am getting a syntax error.

Other than going through 2500 records removing the apostrophies, can anyone
suggest a way round the issue. I guess I need some more quote marks in my
syntax, but not sure where
Click to expand...
Hi Alex,

One typical method would be to change
the single quote in LName to 2 single quotes.

Dim strLName As String

strLName = Replace(LName, "'", "''",1,-1,1)

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & strLName & "'WHERE [SWPurchaseId]=" & SWID

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Hi,
This will deal with single quotes in the text string:

CurrentProject.Connection.Execute "Update tblSWPurchases SET [LicenceeName]
=""" & LName & """ WHERE [SWPurchaseId]=" & SWID
 
Dan

Thanks for that I am still trying to get my head around these quotes when
and where the different types are required. Why does this one need 3 double
quotes each side?

Thanks

Alex


PS Of course it worked !!


dan artuso said:
Hi,
This will deal with single quotes in the text string:

CurrentProject.Connection.Execute "Update tblSWPurchases SET [LicenceeName]
=""" & LName & """ WHERE [SWPurchaseId]=" & SWID


--
HTH
Dan Artuso, MVP


APH said:
Hi I am using the following code to update a record:

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & LName & "'WHERE [SWPurchaseId]=" & SWID

LName is a public variable and contains a text string. occassionally
however, the text string itself contains an apostrophe (eg St Gabriel's)and
when it does I am getting a syntax error.

Other than going through 2500 records removing the apostrophies, can anyone
suggest a way round the issue. I guess I need some more quote marks in my
syntax, but not sure where

thanks

Alex
Click to expand...
Click to expand...
 
One word of warning -- the usage of "" to become a single " embedded in the string will work just fine as long as you stay with MS Access using the Jet Engine (e.g. a .mdb); however, if you ever want to upsize or port to an industrial strength DB, the replacing the ' mark with two apostrophes '' is more portable.

-e
 
Thanks Paul - understand now.

Cheers to everyone who help me on this one

Alex

Paul said:
The additional double quotes put a double quote character into the string
you are creating. You need extras so that Access does not get confused about
where you are saying 'include " in my string' and where you are saying 'this
is the end of my string'.

Use the immediate window (Ctrl+G to display) to experiment - enter

?""" & LName & """ WHERE [SWPurchaseId]=" & SWID

and you will be able to see what is going on.


APH said:
Dan

Thanks for that I am still trying to get my head around these quotes when
and where the different types are required. Why does this one need 3 double
quotes each side?

Thanks

Alex


PS Of course it worked !!


dan artuso said:
Hi,
This will deal with single quotes in the text string:

CurrentProject.Connection.Execute "Update tblSWPurchases SET [LicenceeName]
=""" & LName & """ WHERE [SWPurchaseId]=" & SWID


--
HTH
Dan Artuso, MVP


Hi I am using the following code to update a record:

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & LName & "'WHERE [SWPurchaseId]=" & SWID

LName is a public variable and contains a text string. occassionally
however, the text string itself contains an apostrophe (eg St
Gabriel's)and
when it does I am getting a syntax error.

Other than going through 2500 records removing the apostrophies, can
anyone
suggest a way round the issue. I guess I need some more quote marks
Click to expand...
in
my
syntax, but not sure where

thanks

Alex
Click to expand...
Click to expand...
Click to expand...
 
The additional double quotes put a double quote character into the string
you are creating. You need extras so that Access does not get confused about
where you are saying 'include " in my string' and where you are saying 'this
is the end of my string'.

Use the immediate window (Ctrl+G to display) to experiment - enter

?""" & LName & """ WHERE [SWPurchaseId]=" & SWID

and you will be able to see what is going on.


APH said:
Dan

Thanks for that I am still trying to get my head around these quotes when
and where the different types are required. Why does this one need 3 double
quotes each side?

Thanks

Alex


PS Of course it worked !!


dan artuso said:
Hi,
This will deal with single quotes in the text string:

CurrentProject.Connection.Execute "Update tblSWPurchases SET [LicenceeName]
=""" & LName & """ WHERE [SWPurchaseId]=" & SWID


--
HTH
Dan Artuso, MVP


APH said:
Hi I am using the following code to update a record:

CurrentProject.Connection.Execute "Update tblSWPurchases SET
[LicenceeName] ='" & LName & "'WHERE [SWPurchaseId]=" & SWID

LName is a public variable and contains a text string. occassionally
however, the text string itself contains an apostrophe (eg St Gabriel's)and
when it does I am getting a syntax error.

Other than going through 2500 records removing the apostrophies, can anyone
suggest a way round the issue. I guess I need some more quote marks in my
syntax, but not sure where

thanks

Alex
Click to expand...
Click to expand...
Click to expand...
 
Back
Top