Apostrophy fails process

[Guest]

My form has a text field [FetchProj] used to identify the criteria for a
query supplying data for a form "Assembly Processing". The contents of that
text field might include an apostroph, such as in the string Dolphin's Cove.
The form is opened by a command button event that activates the following
code:

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Assembly Processing"

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

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

When the criteria field includes an apostrophy, an error message reports a
command is missing, perhaps one or more "&" signs, in the following line:

stLinkCriteria = "[UniqID]=" & "'" & Me![FetchProj] & "'"

The criteria variable may or may not include an apostrophy but it must be
able to accomodate either condition. I understand the problem is associated
with the nested apostrophy, but I don't know what to do in the sentax to get
around the problem.

I guess this is my "degree all in moment" and I could use some help.

 

[fredg]

My form has a text field [FetchProj] used to identify the criteria for a
query supplying data for a form "Assembly Processing". The contents of that
text field might include an apostroph, such as in the string Dolphin's Cove.
The form is opened by a command button event that activates the following
code:

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Assembly Processing"

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

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

When the criteria field includes an apostrophy, an error message reports a
command is missing, perhaps one or more "&" signs, in the following line:

stLinkCriteria = "[UniqID]=" & "'" & Me![FetchProj] & "'"

The criteria variable may or may not include an apostrophy but it must be
able to accomodate either condition. I understand the problem is associated
with the nested apostrophy, but I don't know what to do in the sentax to get
around the problem.

I guess this is my "degree all in moment" and I could use some help.

stLinkCriteria = "[UniqID]=""" & Me![FetchProj] & """"

For clarity, that's =" " " & Me![FetchProj] & " " " "

 

[Douglas J. Steele]

When the criteria field includes an apostrophy, an error message reports
a
command is missing, perhaps one or more "&" signs, in the following line:

stLinkCriteria = "[UniqID]=" & "'" & Me![FetchProj] & "'"

The criteria variable may or may not include an apostrophy but it must be
able to accomodate either condition. I understand the problem is
associated
with the nested apostrophy, but I don't know what to do in the sentax to
get
around the problem.

stLinkCriteria = "[UniqID]=""" & Me![FetchProj] & """"

For clarity, that's =" " " & Me![FetchProj] & " " " "

Note, though, that that will then fail on strings with double quotes in them
(Ye Olde "Jolly Roger")

To be able to handle all cases, try something like:

"[UniqID]=" & "'" & Replace(Me![FetchProj], "'", "''") & "'"

That's

" ' " & Replace(Me![FetchProj], " ' ", " ' ' ") & " ' "