Linking a textbox to data that contains an apostrophe

[jpeat]

Hello,

I'm having an issue with my database involving text that contains apostrophes. I have created a form with a series of textboxes. I have written code for each textbox telling it to source (and populate itself with) a particular paragraph from a table (when certain other criteria are met).

Some of these paragraphs have words in them which contain apostrophes and this seems to be the issue. When I run the code below, the textbox (Text50) will populate with the correct paragraph of text (even if it includes apostrophes), but when I use this textbox as a reference for a different textbox (Text81), I receive the error "Syntax Error (missing operator) in query expression" - but only when the paragraph of text contains an apostrophe.

[Forms]![User Interface 3]![Text50] = DLookup("[Competency Description]", "[Competency Query 2]", "[Competency Order] = '2.9'")

[Forms]![User Interface 3]![Text81] = DLookup("[Competency Category]", "[Competency Query 2]", "[Competency Description] = '" & [Forms]![User Interface 3]![Text50] & "'")

Any ideas would be very much appreciated!

Many thanks.

[June7]

Try quotes in place of the apostrophes:
"[Competency Description] = """ & [Forms]![User Interface 3]![Text50] & """")

or Chr()
"[Competency Description] = " & Chr(39) & [Forms]![User Interface 3]![Text50] & Chr(39))

or double the apostrophes within the string

"[Competency Description] = '" & Replace([Forms]![User Interface 3]![Text50],"'", "''") & "'")

Review this http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[jpeat]

Hi June7,

Thank you so much, the second option worked perfectly (although I just had to change the 39 to 34)!!!

Many thanks again