quotation mark

  • Thread starter Thread starter maximus
  • Start date Start date
M

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
 
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
Click to expand...


.
Click to expand...
 
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)



maximus said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Try:

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

HTH
Van T. Dinh
MVP (Access)
 
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
Click to expand...
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 + "'"
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top