Quote Marks ( AGHHH!)

[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

 

[Gary Walter]

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

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

 

[dan artuso]

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

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

 

[APH]

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 !!

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 in my
syntax, but not sure where

thanks

Alex

 

[Guest]

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

 

[APH]

Thanks Paul - understand now.

Cheers to everyone who help me on this one

Alex

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.

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 !!

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

in
my

syntax, but not sure where

thanks

Alex

 

[Paul]

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.

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 !!

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 in my
syntax, but not sure where

thanks

Alex