best way to include quote in field

[mdavid]

Hi,


I'm in the process of creating a database for medicinal plants of the Middle East and need to include the phonetic version of Hebrew and Arabic names, these phonetic names are very often hyphenated to indicate pronunciation.
I'm using Access tables and forms to check and edit this data and was wondering if there's any way to work with strings that include a single quote?
Such as replacing the quote with a special character that doesn't cause problems and returning that quote when you need to display the field.

Thanks for any help
David

[aytee111]

Here is a list of special characters that you cannot use, select one that is not on this list: https://support.office.com/en-us/art...__toc272229039

[mdavid]

Hi aytee111, Thanks for your reply, I wanted to know the best way to handle single quotes in strings. Is there an easier way?

[aytee111]

You can replace it with something else or remove it altogether. Not sure what other options there could be.

[orange]

You can have strings with single quote if you double the single quote.
That is O'Hara becomes O''Hara (2 single quotes). The concept is escaping a ' character.

[mdavid]

What's meant by escaping quotes? does that mean "'"​? Is that another option? What's the accepted way of doing this?

[orange]

Did you read the problem and solution at the link provided?

I have had applications where quotes and accented characters were included in the data.
For storage purposes we stored the data in 2 forms ---1) no quotes, no accented characters
and 2) as written.

When processing -- searches were done against the non accented version.
If changes were required/edits etc, use the Replace(accentedField,"'","''").

[mdavid]

Hi Orange, Sorry I missed the link until you pointed it out. Very helpful, thanks.
Does the solution they suggest of adding an extra quote only apply to data you're displaying in forms, and not to SQL? 'cause you say you don't use it for searches and keeping 2 tables in sync seems like a project on its own.

Thanks for your help
David

[orange]

My suggestion was 2 fields in the same table record.

tblA
fld1 value without quote
fld2 value with quote or accented char

Use fld1 for searching (at least that' what we did)

If you have to process the value in fld2 then use

ValueToUse = Replace(fld2,"'","''")

[mdavid]

ok need to play with this myself - using your example can I display fld2 in a textbox, edit it, move/remove the quote and save it back to the table?

Thanks for you help

[Micron]

Not wanting to seem too ignorant of the problem here, but this is only a problem with vba constructed sql, no? I know you can search, delete and append (so I guess update too) data with forms tied to queries or tables with no problem when it's "joey's" instead of "joeys". Not saying things like apostrophes or commas are good things to have in data, and I guess it might be queries and forms today but vba sql tomorow - just wondering if there really isn't an issue at present. I guess I've always had the luxury of clean data...

[June7]

Quotes and apostrophes become an issue when constructing SQL strings in VBA. I deal with the apostrophe using Replace() function but the quote frustrates me so I forbid the use in data - so inch is not represented with ".

I don't have a problem using Access Find/Replace for those characters. Wildcard characters are another issue. Have to escape by surrounding with [].

[ArviLaanemets]

Are those apostrophes (and possibly other characters) needed only for printout/display, or for some data exchange too?. When only for printout/display, then you can consider to use some Unicode character which looks like apostrophe (I'm sure there will be a lot of them), but is out of classic ASCII area.

[mdavid]

Hi Arvi,
This sounds an interesting idea, can I have just one character in unicode, and the rest in ASCII. Does Unicode play well with SQL?

Thanks
David

[ArviLaanemets]

I have used Unicode in Excel, and have no direct experience in Access with it, but Access has CHRW() and ASCW() functions to operate with them, so I think to some extent you can use them - the best will be to check it out.

I think you can insert an Unicode character without problems. When you don't want create your texts entries using formulas only, create a table where you store needed Unicode characters into text field. Then you can use simply copy and paste whenever you need to enter such character into your text.

With queries you have to check it. I think query can return Unicode character without problems, but I'm not sure what happens when you include them in query string (p.e. as element of WHERE clause or JOIN condition).