Syntax error when using names with apostrophe

[inbarik]

i'm getting syntax error message for this button that tries to get name (of
building) with apostrophe. Any idea how to solve this? Note that i must use
name with apostrophe e.g. O'neal:

Private Sub Command76_Click()
On Error GoTo Err_Command76_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = ChrW(1506) & ChrW(1491) & ChrW(1499) & ChrW(1503) & ChrW(32)
& ChrW(1508) & ChrW(1512) & ChrW(1496) & ChrW(1497) & ChrW(32) & ChrW(1502) &
ChrW(1489) & ChrW(1504) & ChrW(1492)

stLinkCriteria = "[Building]=" & "'" & Me![Building] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Stefan Hoffmann]

hi,

i'm getting syntax error message for this button that tries to get name (of
building) with apostrophe. Any idea how to solve this? Note that i must use
name with apostrophe e.g. O'neal:

You need to escape it.

stLinkCriteria = "[Building]="& "'"& Me![Building]& "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

E.g.:

stLinkCriteria = "[Building]='" & _
Replace(Me![Building], "'", "''") & "'"

Or use a user defined function like

Public Function SqlQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SqlQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

in a standard module and

stLinkCriteria = "[Building]=" & SqlQuote(Me![Building])


mfG
--> stefan <--