Quotation Mark Problem (1 Viewer)

[Bello]

Hi all,

There are some situations where we need to take a control value as parameter for searching.

Consider this example:
------------------------------------------------------------------------------
Mytarget = "[MyField] = ' " & MyTextBox & " ' "

If IsNull(DLookup("[MyField]", "MyTable", Mytarget)) Then

'do something

End If
------------------------------------------------------------------------------

The problem is whenever the control itself (in this case 'MyTextBox') contains a single quote, Access will generate a run-time syntax error. (Double quotes also have the same problem if that's how you represent string value)

I am having the same problem when trying to include a control value in SQL statements. Does anyone know how to solve this?

Any advises would be highly appreciated.

Thanks,
Bello

 

[RichardJ]

Hi just add the following whenever you reference a string etc that may have a qoute, single or double...


chr(34) & "Test's out OK" & chr(34))

The chr(34) returns a "

So you code would look like....



Mytarget = "[MyField] = " & chr(34) & MyTextBox & chr(34)

If IsNull(DLookup("[MyField]", "MyTable", Mytarget)) Then

'do something

End If


Hope that helps!!!!

 

[Bello]

Hi RichardJ,

Thanks for your reply.

Yeah, use double quote or chr(34) does solve the problem when your parameter value contains only the single quote.

But the point is, there is a conflict between the quotes that you use to represent a string and the quotes that actually are parts of your parameter value. Consider my example above, even if I use chr(34) instead of ('), whenever 'MyTextBox' contains a double quote, Access will think that it is already the end of string. Hence still generate a syntax error for your extra quotes.

What I need to know is: Given my example above, how to prevent the syntax error if 'MyTextBox' actually contains BOTH single and double quotes?

Thanks,
Bello

 

[dsmaj]

I'd like to know too...

Apologies for bringing this post back from the dead, but I would like to know how to allow for DOUBLE quotations marks in an SQL string as well....anyone?

Thanks,
Sam

 

[Jon K]

In text strings, two consecutive quotes will return one quote, so we can use:-

Mytarget = "[MyField] = """ & Me.MyTextBox & """"

 

[Pat Hartman]

I don't have a link to it but sometime in the not too distant past, someone posted a function that doubled the quotes or apostrophes in a string. So, if you gave it That's Nice it would return That''s Nice so that you wouldn't have any problems whether you used ' or " within a string. Something like 6'2" would end up as 6''2"" - Try searching for it.

 

[MikeAngelastro]

Perhaps we could convince MS to use a character that is never used when writing like "~" to designate strings. Maybe they could make up a new character for this.

 

[Mile-O]

Nothing to do with MS - it's always been " " to denote strings in BASIC, nevermind Visual Basic.

 

[Kodo]

stringVar="O'hara"
SanitizedStringVar=replace(stringVar,"'","''")

 

[MikeAngelastro]

If not MS, how about a world consortium? Could this be an instance of the computer programming community forgetting that their products are used by human beings? What should we say to the O'Haras of the world? Having to use a quote santitizing function every time we access a string is a waste of resources - especially since the vast majority of the time no quote will be found. If there is is even the minutest chance that the field will contain a quote, the function will be needed. The other question is how do you change a large program so that the function is used when is may be needed?

 

[dsmaj]

Thanks

Thanks for the replies. I couldn't find that post you're talking about Pat, however coding my own function was simple enough. However, are there any negative performance issues with using the replace() function? Would it be better for me to manually parse my input string character by character? The reason I ask is obvious, as this function is going to be run on every string field in my application.

Thanks,
Sam

 

[Kodo]

Character by character would use more resources. Replace function is more efficient.

 

[Pat Hartman]

as this function is going to be run on every string field in my application

- it shouldn't be. You only need to run the function in a search. If you replace all the ' and " characters permanently, your data will look funny and I don't mean ha ha funny.

 

[dsmaj]

- it shouldn't be. You only need to run the function in a search. If you replace all the ' and " characters permanently, your data will look funny and I don't mean ha ha funny.

Hah, that post actually made me laugh in real life..."ha ha funny..." Anyhow, I probably wasn't very clear--I'm actually only running the query on searches and insert/append queries...not for _every_ string. Thanks for the input though, Pat.

Have a good weekend,
Sam