quotation mark

[maximus]

Hi!
I'm having a problem with inserting string value with
single quotation mark form Access form to SQL 2000 table.
My code looks like this:

Dim IDBox as String
Dim WhereStr as String
IDBox=[Forms]![Orders]![OrdersDetail].[Form]!
[ProductDesc].Value
WhereStr = "ProductDesc=" + "'" + IDBox + "'"
DoCmd.OpenForm "OrdersProdDescAutoFill",acNormal, ,WhereStr

When IDBox='Don't sit while you stand!' , I'm getting
error message: "There was a problem accessing a property
or method of OLE object."
Please advise how can I include quotation mark in WhereStr
Thank you

 

[Partha Mandayam]

use two quotation marks

 

[maximus]

Where in code should I use 2 quotation marks?

-----Original Message-----
use two quotation marks

Hi!
I'm having a problem with inserting string value with
single quotation mark form Access form to SQL 2000 table.
My code looks like this:

Dim IDBox as String
Dim WhereStr as String
IDBox=[Forms]![Orders]![OrdersDetail].[Form]!
[ProductDesc].Value
WhereStr = "ProductDesc=" + "'" + IDBox + "'"
DoCmd.OpenForm "OrdersProdDescAutoFill",acNormal, ,WhereStr

When IDBox='Don't sit while you stand!' , I'm getting
error message: "There was a problem accessing a property
or method of OLE object."
Please advise how can I include quotation mark in WhereStr
Thank you

.

 

[Douglas J. Steele]

You need to replace the 1 single quote with two single quotes.

Assuming you're using Access 2000 or newer, try:

WhereStr = "ProductDesc=" & "'" & Replace(IDBox, "'", "''") & "'"

To make it more obvious, that's Replace(IDBox, " ' ", " ' ' ")

If you using Access 97 or earlier, you'll need to write your own equivalent
function for Replace.

BTW, you should use & as the concatenation operator with strings, not +.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Where in code should I use 2 quotation marks?

-----Original Message-----
use two quotation marks

Hi!
I'm having a problem with inserting string value with
single quotation mark form Access form to SQL 2000 table.
My code looks like this:

Dim IDBox as String
Dim WhereStr as String
IDBox=[Forms]![Orders]![OrdersDetail].[Form]!
[ProductDesc].Value
WhereStr = "ProductDesc=" + "'" + IDBox + "'"
DoCmd.OpenForm "OrdersProdDescAutoFill",acNormal, ,WhereStr

When IDBox='Don't sit while you stand!' , I'm getting
error message: "There was a problem accessing a property
or method of OLE object."
Please advise how can I include quotation mark in WhereStr
Thank you

.

 

[Van T. Dinh]

Try:

WhereStr = "[ProductDesc] = " & _
Chr$(34)& IDBox & Chr$(34)

HTH
Van T. Dinh
MVP (Access)

 

[maximus]

Thank you Doug ,it worked!

-----Original Message-----
You need to replace the 1 single quote with two single quotes.

Assuming you're using Access 2000 or newer, try:

WhereStr = "ProductDesc=" & "'" & Replace (IDBox, "'", "''") & "'"

To make it more obvious, that's Replace (IDBox, " ' ", " ' ' ")

If you using Access 97 or earlier, you'll need to write your own equivalent
function for Replace.

BTW, you should use & as the concatenation operator with strings, not +.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Where in code should I use 2 quotation marks?

-----Original Message-----
use two quotation marks
"maximus" <[email protected]> wrote

in
message

Hi!
I'm having a problem with inserting string value with
single quotation mark form Access form to SQL 2000 table.
My code looks like this:

Dim IDBox as String
Dim WhereStr as String
IDBox=[Forms]![Orders]![OrdersDetail].[Form]!
[ProductDesc].Value
WhereStr = "ProductDesc=" + "'" + IDBox + "'"

DoCmd.OpenForm "OrdersProdDescAutoFill",acNormal, ,WhereStr

When IDBox='Don't sit while you stand!' , I'm getting
error message: "There was a problem accessing a property
or method of OLE object."
Please advise how can I include quotation mark in WhereStr
Thank you

 

[maximus]

This doesn't work but i got the solution from Doug. Thank
you for trying!

-----Original Message-----
Try:

WhereStr = "[ProductDesc] = " & _
Chr$(34)& IDBox & Chr$(34)

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hi!
I'm having a problem with inserting string value with
single quotation mark form Access form to SQL 2000 table.
My code looks like this:

Dim IDBox as String
Dim WhereStr as String
IDBox=[Forms]![Orders]![OrdersDetail].[Form]!
[ProductDesc].Value
WhereStr = "ProductDesc=" + "'" + IDBox + "'"
DoCmd.OpenForm "OrdersProdDescAutoFill",acNormal, ,WhereS

t

r

When IDBox='Don't sit while you stand!' , I'm getting
error message: "There was a problem accessing a property
or method of OLE object."
Please advise how can I include quotation mark in WhereStr
Thank you