Queries affected by fields with ' in words - Ie: O'Dwyer

[RobH]

I have a project that seems to bomb out when there is a ' in the Surname field.

Reporting
Syntax Error (missing operator) in query expresion '[Surname] = 'O'Dwyer' and [ConsultID]= 16477'

I have query where the string includes a Field [Surname] which i have included as "[Surname] = '" & [Surname] & "'.....

Now its bitting me in the backside when a name includes another ' character


any thoughts or suggestions would be muxh appreciated.


Regards
Rob.

[MikeTheBike]

I have a project that seems to bomb out when there is a ' in the Surname field.

Reporting
Syntax Error (missing operator) in query expresion '[Surname] = 'O'Dwyer' and [ConsultID]= 16477'

I have query where the string includes a Field [Surname] which i have included as "[Surname] = '" & [Surname] & "'.....

Now its bitting me in the backside when a name includes another ' character


any thoughts or suggestions would be muxh appreciated.


Regards
Rob.

Hi

Two suggestions

1) "[Surname] = '" & Replace([Surname],"'","''") & "'

2) "[Surname] = " & Chr$(34) & [Surname] & Chr$(34) & "

The first method replace a single apostrophy with two apostrophies (not a quotation mark!) within the name. This is then interpreted as a single apostrophy !

The second method effectively uses quotation marks as delimiters not apostrophies.

MTB

[RobH]

My Biggest concern is that the [Surname] appears ok on the Output.

It wouldn't look that great if you got a Quotation with Dear Mr O''Dwyer....

the other thing is that this is a searchable or limitable field with a form asking for a Surname or ConsultID to limit the output.

[MikeTheBike]

My Biggest concern is that the [Surname] appears ok on the Output.

It wouldn't look that great if you got a Quotation with Dear Mr O''Dwyer....

the other thing is that this is a searchable or limitable field with a form asking for a Surname or ConsultID to limit the output.

Hi

The replace function in this instance doesn't replace the value held in the field (which is what is displayed), it is only a method of getting the program to parse the criteria string correctly (ie to stop the error!) and therefore return the correct records, and it does seem to work (which my second suggestion does not, in this context anyway).

So give it a try !!

MTB