Apostrophy fails process

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...


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

For clarity, that's =" " " & Me![FetchProj] & " " " "
 
fredg said:
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.
Click to expand...

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

For clarity, that's =" " " & Me![FetchProj] & " " " "
Click to expand...

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], " ' ", " ' ' ") & " ' "
 
Back
Top