UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Apostrophe In Strings, Access 2013    
 
   
SomekindaVB
post Feb 26 2018, 08:44 PM
Post#1



Posts: 264
Joined: 15-December 16



Hi all,

Im trying to find a record from a customer list. One of the customers has an apostrophe in the name

e.g. strCustomerName = MyName's Bob

I thought to resolve it this way

CODE
lngCustomerID = Nz(DLookup("ID", "Tbl_Client", "[Customer Name] =" & Chr$(34) & strCustomerName & Chr$(34)), 0)


However, this isn't working. How can I find the appropriate customer record ID where an apostrophe exists?

Cheers
Go to the top of the page
 
theDBguy
post Feb 26 2018, 09:15 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I’m looking at this on my phone (small screen) but I don’t see anything wrong with it. I think it should work as-is. Unless, maybe you wanted to use Like with a wildcard to match the input within the field.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Feb 26 2018, 10:50 PM
Post#3



Posts: 2,492
Joined: 27-February 09



I remember seeing this like 20 years ago. Don't you have to double up the single quotes in the data, so that you don't get something like

'O'Neill' (3 single quotes)?

You need it to be like this, I think:

'O''Neill' (doubled in the middle).
Go to the top of the page
 
esugiawan
post Feb 26 2018, 11:18 PM
Post#4



Posts: 3
Joined: 26-February 18



imho, why using customer name, i thing is better is using customer code
if you use combobox , make it 2 coloum , column 1 with length =0 for clientId and column 2 for customercode with x , so user can still see customer name in combo box
in afterupdate combobox , you can use cmbclient.column(0) for the key

sorry if i am wrong
Go to the top of the page
 
MadPiet
post Feb 26 2018, 11:28 PM
Post#5



Posts: 2,492
Joined: 27-February 09



While I see your point and agree, I don't think that's the question - the problem is that you search for people by their names... Sure, each Person record should have primary key, but you still have to search for a person by first name and/or last name. And names occasionally contain apostrophes.
Go to the top of the page
 
SomekindaVB
post Feb 26 2018, 11:32 PM
Post#6



Posts: 264
Joined: 15-December 16



Thanks for response all.

In the end I went with double apostrophes, which seems to work. I shall need to test this a few times to make sure i'm getting the right records.

esugiawan, I'm using Customer name, because that's the only piece of data I'm being given from my source, which is used to lookup the ID and other customer information in the customer table.

DBGuy, using like was my first thought, but like is too generic for specific requirements. you can end up with the wrong first record if there are many different similarly name customers.
Go to the top of the page
 
HairyBob
post Feb 27 2018, 02:40 AM
Post#7



Posts: 992
Joined: 26-March 08
From: London, UK


Hi SomekindaVB,

I've just tried your code as is and as theDBGuy said, found it to work. I created a table with 5 entries; Customer Name (Text) and ID (Autonumber), ID 3 Customer Name being O'Neil. The code you posted returned ID 3 when strCustomerName is O'Neil.

Hairy.
Go to the top of the page
 
BruceM
post Feb 27 2018, 08:19 AM
Post#8


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


There are several ways to approach the possibility of a name containing an apostrophe. Doubling the apostrophes has been described. A search for "Access quotes within quotes" or the like will turn up additional information such as this article or this one.

I use the following in almost any situation where I have to concatenate text, from a newsgroup posting by Stefan Hoffman in 2007:

CODE
Public Function SQLQuote(AString As String) As String

   Const Delimiter As String = "'"
   SQLQuote = Delimiter & Replace(AString, Delimiter, Delimiter & Delimiter) & Delimiter

End Function


Use it like this:

strWhere = "LastName = " & SQLQuote(Me.LastName)

I agree that best practice is to use ID numbers instead of last name, and things like that, whenever possible. However, sometimes you will need to work with a text value. The SQLQuote function provides a simple and convenient way to manage that.
Go to the top of the page
 
seguinsoftware
post Feb 27 2018, 04:53 PM
Post#9



Posts: 739
Joined: 4-October 06
From: Maine, US


Whenever writing names to a database you could add the ',

Function GoodString(Optional InputString as string = "") as String
GoodString= Trim$(Replace(Nz(InputString , ""), "'", "''"))
End Function

Then when you search the table you can use a "regular" search string (i.e. "[Name]='" & NameToSearchFor & "'"


--------------------
Stuart
Go to the top of the page
 
PhilS
post Feb 28 2018, 03:37 AM
Post#10



Posts: 500
Joined: 26-May 15
From: The middle of Germany


QUOTE
Then when you search the table you can use a "regular" search string (i.e. "[Name]='" & NameToSearchFor & "'"

Well, no, you can't. - If there is a single quote in the NameToSearchFor this will result in a syntax error. You need to replace (double) the quote whenever you pass such a value to the DB-Engine.

BTW: I just completed my tutorial text on VBA-SQL-Strings. That naturally covers this topic as well.

--------------------
Go to the top of the page
 
BruceM
post Feb 28 2018, 09:22 AM
Post#11


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Phil, that's quite a discussion about quotes. I have bookmarked it for possible reference in a future UA thread. Maybe it could be a UA Wiki item.

However, the code suggested by sequinsoftware seems to do the same general thing as the Delimiters in Text Literals section of the article, toward the end, so I'm not sure why you say it won't work, unless it is because you saw the last argument in the Replace function as (spaces added) " " " rather than " ' ' ".

BTW, the posted GoodString function produces the same result as the function I posted. For my purposes I have not had a need to make the argument optional, and I didn't include Nz or Trim, but if a string is passed to GoodString the result will be the same.

Seguinsoftware, if the function is to allow for the possibility of a null value being passed as the argument, it seems to me the argument should be variant, not string. I don't think the code will get as far as the Nz function if Null is passed as a String argument.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 10:03 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Sure wish that access (or maybe SQL) would substitute some other symbol for single and double quotes when found inside a text string. The same way that systems zip and unzip and encrypt and unencrypt while the user is totally oblivious that any of that is going on. The system could do the same with quotes in strings. Conversely, could just make programmers use different symbols for encapsulating strings.... oh well...another great argument for numeric keys.


If your app owns the data....you could throw away any single or double quotes in any text data in the before save event. Just saying....
This post has been edited by zaxbat: Feb 28 2018, 10:07 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
PhilS
post Feb 28 2018, 10:34 AM
Post#13



Posts: 500
Joined: 26-May 15
From: The middle of Germany


@BruceM: Thanks for the friendly feedback!
QUOTE
However, the code suggested by sequinsoftware seems to do the same general thing as the Delimiters in Text Literals section of the article, toward the end, so I'm not sure why you say it won't work, ...

seguinsoftware suggested you double the quotes once during the insert of the data and then never need to worry again about them when searching. - The latter is what I mean will not work.


@zaxbat:
QUOTE
If your app owns the data....you could throw away any single or double quotes in any text data in the before save event. Just saying....

Well, not the best idea in general, I think. - Not sure what you mean by "If your app owns the data" though.

The original example for the issue was a customer name. Customers will probably not be happy about your application changing their names. If you do business in the EU, you are forbidden by law to so once the GDPR is in effect end of May 2018.

--------------------
Go to the top of the page
 
BruceM
post Feb 28 2018, 10:37 AM
Post#14


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


QUOTE
another great argument for numeric keys

Even with numeric keys there are reasons for using text as criteria, such as when searching a Comments or other free text field. Or it can be used for things like message box advisories:

Bob's Electrical Supply is not an approved supplier for this purchase

That can be assembled like this:

SQLQuote(CustomerName) & " is not an approved supplier for this purchase"

QUOTE
If your app owns the data....you could throw away any single or double quotes in any text data in the before save event

Apostrophes are perfectly valid. Removing an apostrophe from a possessive could lead to a very different meaning. In names it would result in incorrect spelling. Quotes (double quotes) are also valid, particularly in free text fields.

The SQLQuote function I provided earlier (with attribution), and used above, is a simple wrapper for any text value used as criteria or otherwise assembled into a string. I have tested it on strings that include single quotes, double quotes, and both. Quotes of all sorts can be conquered.
Go to the top of the page
 
BruceM
post Feb 28 2018, 10:39 AM
Post#15


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


QUOTE
seguinsoftware suggested you double the quotes once during the insert of the data and then never need to worry again about them when searching

Oh. I thought it was a wrapper function such as I posted, and then demonstrated in my posting previous to this one. But now that I read the accompanying text more carefully, I believe you are correct, in which case I agree.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 11:08 AM
Post#16



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Didn't consider that this could run into legal limitations. You are right then...have to keep them in text. Then, programming languages should create new symbol(s) for single and double quotes as used for text wrappers and they could then treat single and double quotes no different than they treat a, b, or c....etc.... just my two cents..... imagine how much time (and therefore money) is wasted working around this quote debacle.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
BruceM
post Feb 28 2018, 11:40 AM
Post#17


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


QUOTE
programming languages should create new symbol(s) for single and double quotes

And a way to type the new symbol :-)
Go to the top of the page
 
HairyBob
post Feb 28 2018, 12:58 PM
Post#18



Posts: 992
Joined: 26-March 08
From: London, UK


There's a hole in my bucket! laugh.gif
Go to the top of the page
 
zaxbat
post Feb 28 2018, 07:46 PM
Post#19



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Just opened a fortune cookie. It says: It doesn't cost anything to be civil....
This post has been edited by zaxbat: Feb 28 2018, 07:47 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 04:29 AM