Problem with apostrophe in Name Ex: O'neil 3

[peryan77]

I have a combo box on my form and when I select a record from it (employee name) it goes to matching record in subform. However, i get an error when i select an employee name with an apostrophe in it.

Syntax error, missing operator in expression.

Here is my code..it works fine with all the other names.

Dim rs As Object
Set rs = Me.Recordset.Clone
With rs
.FindFirst "[Employee_Name] = '" & Me![cboEmployeeName] & "'"
If Not .EOF Then Me.Bookmark = .Bookmark
End With

 

[RoyVidar]

Hi!

For DAO recordset, as this is, you should be able to get around it using double quoting:

[tt].FindFirst "[Employee_Name] = """ & Me![cboEmployeeName] & """"[/tt]

- no single quotes, but loads of quotes;-)

Roy-Vidar

 

[peryan77]

Thanks soo much it worked. I did not know about this and I have been using access for 4 yrs. Never stumbled upon this dilemma before.

Patrick

 

[PHV]

Another way, working with all sort of recordset, since ac2k:
"[Employee_Name] = '" & Replace(Me![cboEmployeeName], "'", "''") & "'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya peryan77 . . . . .

Just backing up [blue]RoyVidar[/blue] & [blue]PHV[/blue] here.

I always add the following constant to a standard module for just this purpose (espcially for SQL in VBA):

[BLUE]Public Const DQ As String = """"

[BLACK]Thats four Double Quotes![/BLACK][/BLUE]

Now instead of typing:
[blue]FindFirst "[Employee_Name] = '" & Me![cboEmployeeName] & "'"[/blue]
I type:
FindFirst "[Employee_Name] = " & [purple]DQ[/purple] & Me![cboEmployeeName] & [purple]DQ[/purple]

This is the bestway to go, since as you've found out, single quotes in strings can raise hell.

For a decent critique on the matter, open a VBA module, hit F1, and in the Help Wizard, type [purple]Quotation Marks In Strings[/purple]. Its pretty revealing . . . . .

See Ya! . . . . . .

 

[scriverb]

TheAceMan1: Star for you. I think this idea is a great one and will be very helpful many here at TT. Never have had a problem understanding single and double-quote problems but have tutored many that have struggled with this issue. This idea will certainly help them in building appropriate criteria strings.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

I'd like to know if the DQ ("""") solution works with ADO and/or SQL Server BE.

 

[TheAceMan1]

How are ya PHV . . . . .

Since the DQ constant, as well as single quotes, are strictly tied to [purple]Embedding Strings in SQL in VBA[/purple], [blue]ADO[/blue] has nothing to do with it, and I expect it to work with [blue]SQL Server BE[/blue], as I've never heard of or learned about, any other method to embed strings in SQL.

Presently, I do not have access to [blue]SQL Server[/blue] and can't qualify that it does work. Hopeing someone will stop by and make a quick test.

I'm assumeing here, we are all aware of Embedded Strings in SQL, and are knowledgable of the the problem with single quotes. So I won't go into this unless prompted.

See Ya! . . . . . .

 

[RoyVidar]

Hmmm - TheAceMan1 - that's not the same results I get on my setup. I've found some differences between using DAO methods and ADO methods.

Here's some sample code I've used for testing, would you care trying to see if you get the same results?

I've also included another ADO "double qouting" equivalence workaround, using hash (#) (see the ADO???.chm file, located somewhere on the harddrive on the .Find method), and below is a table of which criterion worked with which .Find/.Filter method on my setup.

[tt] Dim rs As ADODB.Recordset
Dim sCrit As String
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open "MyTable", , , , adCmdTable
If Not .BOF And Not .EOF Then
.MoveFirst
sCrit = "O'Neil's" ' S1
'sCrit = "'t*'e*'s*'t" ' S2
.Find "MyField=""" & sCrit & """" ' F1
'.Find "MyField=#" & sCrit & "#" ' F2
'.Find "MyField='" & Replace(sCrit, "'", "''") & "'" ' F3
'.Filter = "MyField=""" & sCrit & """" ' F4
'.Filter = "MyField=#" & sCrit & "#" ' F5
If Not .EOF Then
Debug.Print .Fields(0).Value, .Fields(1).Value
End If
End If
.Close
Set rs = Nothing
End With
If (Not (rs Is Nothing)) Then
If (rs.State = adStateOpen) Then
rs.Close
End If
Set rs = Nothing
End If[/tt]

Table of findings:
[tt]
F1 F2 F3 F4 F5
S1 No Yes Yes No Yes
S2 No Yes Yes No Yes[/tt]

Roy-Vidar

 

[TheAceMan1]

How are ya Roy . . . I don't know about anyone else, but its always an honor to have you stop by! . . . . . I'll do the best I can to explain . . . . .

For all your testing, you forgot to include my DQ constant! Which if ya tried it, you would see it would pass ([purple]already done that[/purple])!

Also, for all the ADO/DAO functionality you wish to prove out, you don't realize you've proven my point. ADO/DAO functionality, has nothing to do with [blue]Embedding Strings In SQL[/blue].

Albeit, I'm not an ADO guru yet (taking classes for the past six months), but if you parse through the code you gave me, you'll find when it comes to assigning the [blue]sCrit[/blue] criteria string, there's no ADO/DAO functionality involved. Your simply trying to put the criteria in an format acceptable to the [blue]Jet[/blue] for parsing. And this is the hub of it all. [purple]The criteria string has to be in the proper format for parsing by the Jet.[/purple] This is no different than designing a query in design view and then switching to SQL view. SQL view shows the query in its proper format, since [purple]Access does the embedding for you![/purple]. As an SQL guru (I believe), this is the reason I no longer design SQL. I make a query to do what I want, and just copy the SQL if I need it! . . . . . I'm sure you've done the same.
You mentioned the use of the hash(#) mark in ADO. I'm a little confused here because to my knowledge,ADO/DAO aside, the hash marks are used to signify dates to the Jet. If I'm wrong, please correct me!

To summarize: The DQ constant works as far as embedding strings in SQL is concerned. All the other variations are not a matter of my concern in this thread. The DQ constant is! Its simply my way of making it a little easier.

So . . . . what ya think Roy!

See Ya! . . . . . .

 

[PHV]

And what happens with sCrit = "she's saying ""Hello!"""
I personally use this:
" WHERE MyField='" & Replace(sCrit, "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

To all . . . . .

My point is this thread is that [blue]the DQ constant works[/blue]. All other possible variations of concatening a string in proper format for parsing by the Microsoft Jet, are irrevalent. Since DQ works, and very easily, and relieves the programmer of such intent focus to see that concatening is done properly, this is all I would ever use.

I normally don't challange unless I'm that sure of myself. So, espcially since this thread is heading far off the mark, I query anyone to prove the DQ constant does'nt work! Just prove it doesn't work!
GoodLuck! . . . . . .

See Ya! . . . . . .

 

[scriverb]

I agree with The AceMan as I have already awarded the Star. I have tutored this string cancatenation issue to many programmers and to some it is impossibile to keep the string double-quotes seperate from the single quotes with the string or the double-double quotes within the string which designate a string value. Now if that is confusing to you maybe I should use the DQ within my reply.

This is how simple TheAceMan's idea is. When cancatenating strings and using double-quotes around them the use of DQ to designate a character within the string is easy for newbies to understand. I passed this idea along to a couple of newer programmers and it was if the lights were turned on.

And this has nothing to do with ADO vs DAO. Strings are strings and Jet interprets SQL as a string and requires either a matching set of singles or matching set of doubles to designate a string value for comparison. It is only in the VBA side that this is used and the results will be the same whether you use DQ or ' or "". You end up with a quote character.

Just my 2cents worth.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[RoyVidar]

Thank you both scriverb and TheAceMan1 for your persistent effort in addressing the issue!

So sorry for the late update, it's been a bit hectic here (not related to Tek-Tips)

However the tests I performed yesterday, included using both publics, locals, variables and constants assigned four double quotes (and chr(34) for the variables) nothing worked on my computers (urged by the intensity, percistance and absolute sureness of your replies, I redid all the tests today, but to no avail), what am I doing wrong??? (quite a novice at this, haven't even had any classes).

Would any of you Gentlemen, scriverb or TheAceMan1 be so kind as to post the code you used in your tests of the .Find method of the ADO recordset with the DQ, so I can get this working here too? That would be awfully kind!

BTW, any of you Gentlemen have a copy of Access 2000 Developer's Handbook by Ken Getz, Paul Litwin and Mike Gilbert, SYBEX? Pages 282-287 are pretty interesting (they can be viewed online too - find the section "Using Variables in Strings" here Chapter 6: ActiveX Data Objects.

Oh - almost forgot - using hash (#) for the ADO .Find method is addressed in the same section of the above link. Oh - in the same section there is a quote that might indicate I'm not entirely alone in my (obviously wrong assumptions) that there might be some tiny difference between ADO .Find and DAO .FindFirst:

WARNING With the old DAO FindFirst method, you had several other alternatives to solving this problem, based on the fact that DAO would accept double-quote characters to delimit a string in a FindFirst criterion. However, ADO requires single quotes in this context.

Not sure if it relates to the issue, though.

Oh - yeah - your missing ADO documentation, the same help topics can be viewed here (string delimiters):
Filter Property, Find Method

Thank you!

Roy-Vidar

 

[TheAceMan1]

How are ya Roy . . . . .

First things first . . . . [purple]Your Right![/purple]. Not in ADO Recordset.

I didn't do your testing, but I remembered a DB we tested this with in class (this was last month). I went nuts lookin for it. I thought I'd get my references there. But at the bottom I have a note to myself:

[blue]Do not use ADO Recordset (unless absolutely necessary) for the following reasons:
1) ADO Recordset only allows one field.
2) ADO Recordset cannot handle strings with apostrophes.
3) ADO Recordset requires more code to accomplish the same as DAO.
4) SQL Server uses single quotes for strings. Double Quotes are reserved for Database Objects.[/blue]

I tested ADO Recordset with an SQL statement varing the criteria using DQ. Thank God it still works there, but again, strings can't have single quotes.

I've totally lost it here and have no Idea if I'll recover. I'm not even sure if I know right from wrong . . . . .

See Ya! . . . . . .

 

[scriverb]

Well Roy, hats off to ya. You are correct. ADO criteria expressions do not accept the double-quotes within the strings. My mistake.

I was referring more from a SQL postion in using the DQ in a string I did mistakenly say that there should be no difference between DAO and ADO. ADO, is in fact different and must use the single quotes only. I have learned something new here so thanks for the update and lesson.

Never too old to learn something knew. That's why we all post here at TT anyway.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TheAceMan1]

Hay Roy!

Was finally able to pick myself up off the floor ;-)

Something I was afraid of has happened as an result of this thread.

Understand . . . . I challanged and got what I asked for . . . . I can live with that. But a greater problem came to my mind as a result of this thread, that I had to take care of.

I really need you here : thread705-863343

See Ya! . . . . . .

 

[RoyVidar]

Ah - so you did catch me lying at last, well yes, I'm not exactly a novice in ADO, though I'm no expert. Converted to ADO in 2000, and, well - in stead of just doing a very strong "double quoting simply does'nt work with the ADO .Find method, and that's final", I tried to offer my second reply in this thread as a "graceful" way out for you. But, I'm afraid I totally lost my calm when neither PHV's nor my hints where able to trigger neither testing nor a quick glance in the documentation. Sorry!

Didn't mention why I included the ADO .Filter property to the test and discussion. The .Find method only allows a one column criteria/serch, but the .Filter property allows multicolumn "find", and also has some other advantages over the .Find method (see for instance thread705-774978, where faeryfyrre compiled a little list on .Find/.Filter and some links with samples).

I wish you good luck in your conversion to ADO, the differences between DAO and ADO might seem a bit "overwhelming" in the start, but luckily there are a lot of good recources. Tek-Tips being one of them. Even though it seems DAO is the preferred means of manipulating recordsets by members of Tek-Tips (access developers in general?), there are some Tek-Tips members having vast experience and knowledge in ADO. I am not among those, but a Keyword search using my signature and "ADO", might perhaps be of some use concerning some of the ADO recordset basics. One thing that might prove frustrating, is opening stored queries with parameters from forms (thread709-819033).

Short note, ADO is the default recordset library in a2k and xp, in a2k3 both libraries are checked by default when creating new db's, but DAO is still needed, because the form recordsets bound forms in Access databases (not ADP's), are still DAO a2k thru a2k3 (only when using ADP, also the form recordsets defaults to ADO). You can bind Access database forms to ADO recordsets thru code too, but, there are some "quirks" relating to that, at least for a2k.

So, all out in the open, you now know I knew from the start, I did hovewer not lie about testing. I try to always test the suggestions I give when replying here (in fact, I even did a very quick test with the double quoting on the DAO .FindFirst before my first reply in this thread too), and especially when offering divergent opinions than those already present in a thread, even if I know for sure they are wrong. I think I'll revert to being a bit rude again, and recommend that;-) Not having English as first language, imposes some limitations regarding both reading and writing. I've tried to impose the rule upon myself to at all measn avoid phrasing or wording that might be offensive to others, but must confess I totally lost it when writing my third reply. Again, apologies!

Roy-Vidar

 

[TheAceMan1]

Roy . . . . .

No apologies necessary, in fact, always remember this:

[blue]Never entertain Ignorance . . . .
&
[purple]NEVER ENTERTAIN BEING WRONG WHEN YOU KNOW YOUR RIGHT![/purple][/blue]

The ADO RecordSet Filter property is working just fine. Its all I needed to complete my upgrade code, and its upgrading to ADO thats always bothered me (in the back of my mind) for the past couple of years. I've always known that sooner or later, it would have to be done.

I have to say though, even with classes, ADO itself has turned out to be a bit of a nightmare. Its practically a new language, Data Modeling & Objectivity are geared more towards server, and a number of methods have been revamped like the Find method.

Besides that, there are a number of [blue]ambiguities[/blue] that don't make any sense. Take the Double Quotes issue for instance. Although ya can't use them in ADO Recordsets, you can still open any query in design view, type a non-delimited string in the criteria line for any text field, goto SQL view, and [purple]see where Access has inserted Double Quotes for you![/purple] In this case, why maintain tried & true in one instance and not in recordset is an answer I havn't been able to get yet. I've written microsoft about this quite a few times but . . . . . . .

Well . . . . be that as it may I still have ta get on with it. Thanks for your help in this matter, and I'd just like to say:

[purple]My respect for you, has not waivered one pico-inch[/purple] . . . . .

See Ya! . . . . . .

 

[sha76]

Time to revive an old thread! Really I'm just doing this for purely selfish reasons as I knew it had the answer I wanted & it's taken me forever to track it down, at least next time it'll be in my replies list!

On the other hand as I was searching I found many people had the same question and this post contains the best solutions I've seen, so hopefully others will find it useful.

"Your rock is eroding wrong." -Dogbert