buggy form filter control

[Toireasa]

Hi all,

In my form, where, each individual record is a Subject with various data attached, I've set up a combo box to select which record I want to look at. The macro doing this is a SearchForRecord where:

, , First, ="[PathwayName] = " & "'" & [Screen].[ActiveControl] & "'"

This works fine most of the time. The problem comes when some of the subjects have apostrophes in their names; for example, "Crohn's disease". I understand that this is because this is the same character as the text delimiter, but that's where my debugging grinds to a halt, because I don't know how to go about making this *not* an issue.

Anyone got any advice for me? I'm using Access 2007, and prefer macros to VBA as I'm not an advanced programmer, so I wouldn't know where to start with sorting this out through the real code.

Thanks, and Merry Christmas,
Toireasa

[ChipR]

Instead of surrounding your string variables with the apostrophe you can use two double quotes like "". They will be evaluated to 1 double quote in the resulting string.

"[PathwayName] = "" " & [Screen].[ActiveControl] & " "" "
I spaced them out for clarity.

I would normally write
"[PathwayName] = """ & [Screen].[ActiveControl] & """"

[Toireasa]

Thanks Chip - that works perfectly!

[NeoPa]

Check out Quotes (') and Double-Quotes (") - Where and When to use them.

I would suggest wrapping the reference up in a wrapper function that doubles any single-quotes found.