Apostrophe Catastrophe

  • Thread starter Thread starter Toffee
  • Start date Start date
T

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?
 
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], "'", "''") &
"'")
 
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?
Click to expand...


.
Click to expand...
 
Back
Top