Apostrophe Catastrophe

[Toffee]

I am having a problem with apostrophes in text fields used
in domain aggregate functions. I am developing a report
that searches records for duplicate addresses and uses the
following function in the SQL statement for the report:

DSum(1,"Customers","Address='" & [Address] & "'")

All goes well until this function happens upon a street
named after an Irishman. In this case, it happens to be
an address with a street named O'Leary Lane that stops the
process and gives me the following error:

Syntax error (missing operator) in query
expression 'Address='107 O'Leary Lane"

Any suggestions other than eliminating all punctuation in
street names?

 

[Ken Snell]

If you have ACCESS 2000 (Jet 6, I think?) or higher, you can use the Replace
function to double up the ' characters (two ' characters in a row is
interpreted by ACCESS as a single, actual ' character and not a delimiter:
DSum(1,"Customers","Address='" & Replace([Address], "'", "''") &
"'")

If you can't use Replace function in your statement, if you have A2K, you
can write your own function and call it from the query. Put this function in
a regular module:

Public Function RReplace(strText As String, strFind As String, strReplace As
String) As String
RReplace = Replace(strText, strFind, strReplace)
End Function

Then the DSum would look like this:
DSum(1,"Customers","Address='" & RReplace([Address], "'", "''") &
"'")

 

[Toffee]

That appears to have done the trick. Thank you.

-----Original Message-----
If you have ACCESS 2000 (Jet 6, I think?) or higher, you can use the Replace
function to double up the ' characters (two ' characters in a row is
interpreted by ACCESS as a single, actual ' character and not a delimiter:
DSum(1,"Customers","Address='" & Replace ([Address], "'", "''") &
"'")

If you can't use Replace function in your statement, if you have A2K, you
can write your own function and call it from the query. Put this function in
a regular module:

Public Function RReplace(strText As String, strFind As String, strReplace As
String) As String
RReplace = Replace(strText, strFind, strReplace)
End Function

Then the DSum would look like this:
DSum(1,"Customers","Address='" & RReplace ([Address], "'", "''") &
"'")



--
Ken Snell
<MS ACCESS MVP>

I am having a problem with apostrophes in text fields used
in domain aggregate functions. I am developing a report
that searches records for duplicate addresses and uses the
following function in the SQL statement for the report:

DSum(1,"Customers","Address='" & [Address] & "'")

All goes well until this function happens upon a street
named after an Irishman. In this case, it happens to be
an address with a street named O'Leary Lane that stops the
process and gives me the following error:

Syntax error (missing operator) in query
expression 'Address='107 O'Leary Lane"

Any suggestions other than eliminating all punctuation in
street names?

.