Apostrophe problem in code

  • Thread starter Thread starter R. Choate
  • Start date Start date
R

R. Choate

I have a form with a couple of combos. The first combo filters the available records for the second combo. After update on the 2nd
combo brings up a single record to the form. My problem is that when there is an apostrophe in the text in either combo, it throws
an error of invalid syntax. I understand that the apostrophe is erroneously telling Access that there is text beginning, or
basically serving as a single quote for which there is no matching end quote. Whatever the case, I need to make this work without
eliminating the apostrophe. I'm sure there is a slight adjustment to my syntax which would fix my problem. Here is my current code
for one of the boxes:

If Me.[Tenant].ControlSource = "" Then
DoCmd.ApplyFilter , "find.[Tenant] = '" & Me.[Tenant] & "' AND [find].[Project Name] = '" & Me.[Project Name] & "'"
End If

Please forgive that the combos have improper naming and so forth. I did not build this db and correcting all of its myriad little
"issues" is beyond the scope of my work. Please help me find a way to have it work where I don't get an error when the filter
requires "Taylor's" = "Taylor's" and so forth.
 
Use the Replace function to double-up ' charactersr within the string:

DoCmd.ApplyFilter , "find.[Tenant] = '" & Replace(Me.[Tenant], "'", "''",
1, -1, 1) & "' AND [find].[Project Name] = '" & Replace(Me.[Project Name],
"'", "''", 1, -1, 1) & "'"
 
I have a form with a couple of combos. The first combo filters
the available records for the second combo. After update on
the 2nd combo brings up a single record to the form. My
problem is that when there is an apostrophe in the text in
either combo, it throws an error of invalid syntax. I
understand that the apostrophe is erroneously telling Access
that there is text beginning, or basically serving as a single
quote for which there is no matching end quote. Whatever the
case, I need to make this work without eliminating the
apostrophe. I'm sure there is a slight adjustment to my syntax
which would fix my problem. Here is my current code for one of
the boxes:

If Me.[Tenant].ControlSource = "" Then
DoCmd.ApplyFilter , "find.[Tenant] = '" & Me.[Tenant] & "' AND
[find].[Project Name] = '" & Me.[Project Name] & "'" End If

Please forgive that the combos have improper naming and so
forth. I did not build this db and correcting all of its
myriad little "issues" is beyond the scope of my work. Please
help me find a way to have it work where I don't get an error
when the filter requires "Taylor's" = "Taylor's" and so forth.
Click to expand...

There are four (4) single quotes in the above code. Replace each
of those with a pair of double quotes.
 
Thanks Ken! Long time no talk.

I knew that replace was the likely solution but my approach was wrong. How does doubling up solve the single quote problem?

Richard
--
RMC,CPA


Use the Replace function to double-up ' charactersr within the string:

DoCmd.ApplyFilter , "find.[Tenant] = '" & Replace(Me.[Tenant], "'", "''",
1, -1, 1) & "' AND [find].[Project Name] = '" & Replace(Me.[Project Name],
"'", "''", 1, -1, 1) & "'"
 
ACCESS and Jet interpret a '' (two consecutive ' characters) within a text
string delimited by ' characters as a single, embedded ' character. Similar
result for embedded " character when the text string is delimited by "
character -- doubling up to "" yields a single " character.
 
Thank you Ken. That is both immediately helpful and the sort of tidbit I can remember for the future when I know I am going to run
into this again and again.

Good to hear from you.

Richard
--
RMC,CPA


ACCESS and Jet interpret a '' (two consecutive ' characters) within a text
string delimited by ' characters as a single, embedded ' character. Similar
result for embedded " character when the text string is delimited by "
character -- doubling up to "" yields a single " character.
 
Back
Top