VBA errors when apostrophe or double quotes are in String value

[shanesuzanne]

The code below takes a table and adds a count to the rows based on unique values. Here is an example of it's output:

row coName Sequence
1 ABC 1
2 XYZ 1
3 ABC 2
4 RST 1
5 ABC 3
6 XYZ 2

It works perfectly - EXCEPT when there is an apostrophe or a double quote in the value - and I have both. Since the coName field is the legal company name, I don't want to remove the punctuation from the data in the table. I've tried different approaches for hours, but I can't get it to work. Here is where it has the error: coName = '" & tblDistinct.Fields("coName").Value &

I get run-time error -2147217900 (80040e14) "Syntax error (missing operator) in query expression 'coName='ABC's' 'Order By Record".

Any ideas?

Sub addSeq()
Dim tblDistinct As New ADODB.Recordset
Dim Count As New ADODB.Recordset

tblDistinct.Open "Select Distinct coName From imports", Application.CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do Until tblDistinct.EOF
    Count.Open "Select * From imports Where coName = '" & tblDistinct.Fields("coName").Value & "' Order By Record", Application.CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    Do Until Count.EOF
        Count.Fields("Sequence") = Count.AbsolutePosition
        Count.MoveNext
    Loop
    Count.Close
    F4.MoveNext
Loop
tblDistinct.Close
End Sub

 

[Micron]

At the moment, all I can think of is using a combination of the Left function on x number of characters and the LIKE operator with a wild card (*) OR
adding an indexed field to replicate the name without special characters and making that the primary field for searching, but the current field for display. I don't think any tricks involving the Replace function to remove the characters is going to help because that value won't be found in the table. The trouble with the first suggestion is, how many characters is enough and at what point will that result in two values that meet the criteria when only one record would be desired?

You could use the Replace function to rapidly create these new searchable names.

 

[james_lankford]

see the answer marked with the green check mark
ms access - Escaping unwanted characters, mainly single quotes --replace function and implementation - Stack Overflow

and since you said you have double quotes and single quotes
you might have to call the replace function twice --- once for doubles, once for singles

or maybe you could do call the instr function to check if you have a double or single and then call the replace function that is necessary

 

[xenou]

seems like you could write a normal count query:

select coName, count(coName) as total from Table1 group by coName

That would give you the counts. then join to this query/table and update the count field in the original table (which by the way isn't completely necessary since the above query gets the counts for you without having to store the counts.

Otherwise, I assume that the links in James' post give the answer for dealing with raw strings with ADO/SQL code that contain apostrophe's - you need to escape them.

 

[Micron]

what you're doing looks rather complicated for what the action seems to be - namely making one field in a table the same as another field. If that's the exercise, why not just run an update query? Nor do I see you doing anything with the recordset that would require ADO over DAO, but then you might be running this against non-Access tables.