Syntax error (missing operator) in query expression '[]='fname o'l

[Guest]

created a command button with the wizard that opens a form based upon a name
in the current form and I get the error: Syntax error (missing operator) in
query expression '[]='fname o'last" when there's an apostrophe in the last
name.

The code the wizard created is:

Private Sub Command620_Click()
On Error GoTo Err_Command620_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Firm Contact"

stLinkCriteria = "[Engineer Name]=" & "'" & Me![Firm Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command620_Click:
Exit Sub

Err_Command620_Click:
MsgBox Err.Description
Resume Exit_Command620_Click

End Sub

Any help is appreciated... I'm a rooky with Access...

 

[John Vinson]

created a command button with the wizard that opens a form based upon a name
in the current form and I get the error: Syntax error (missing operator) in
query expression '[]='fname o'last" when there's an apostrophe in the last
name.

The code the wizard created is:

Private Sub Command620_Click()
On Error GoTo Err_Command620_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Firm Contact"

stLinkCriteria = "[Engineer Name]=" & "'" & Me![Firm Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command620_Click:
Exit Sub

Err_Command620_Click:
MsgBox Err.Description
Resume Exit_Command620_Click

End Sub

Any help is appreciated... I'm a rooky with Access...

The problem is that you're using ' as a delimiter in your
stLinkCriteria, and it's seeing the apostrophe as a closing quote. Use
" instead; to insert a doublequote in a doublequote delimited string
double the doublequote:

stLinkCriteria = "[Engineer Name]=""" & Me![Firm Contact] & """"

This will give you a criterion like [Engineer Name] = "Kevin O'Toole"

Just a warning: names are NOT unique. What happens if you have two
engineers both of whom happen to be named Jim Smith? I'd suggest a
(concealed) unique EngineerID.

John W. Vinson[MVP]

 

[Douglas J. Steele]

You can change the text delimiter to a double quote rather than the single
quote that the wizard used:

stLinkCriteria = "[Engineer Name]=""" & Me![Firm Contact] & """"

or

stLinkCriteria = "[Engineer Name]=" & Chr$(34) & Me![Firm Contact] &
Chr$(34)

Alternatively, assuming you're using Access 2000 or newer, you can use the
Replace function to change the apostrophe to 2 consecutive apostrophes:

stLinkCriteria = "[Engineer Name]=" & "'" & Replace(Me![Firm Contact], "'",
"''") & "'"

 

[Guest]

Thanks to you both, the double quoates worked like a charm!

created a command button with the wizard that opens a form based upon a name
in the current form and I get the error: Syntax error (missing operator) in
query expression '[]='fname o'last" when there's an apostrophe in the last
name.

The code the wizard created is:

Private Sub Command620_Click()
On Error GoTo Err_Command620_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Firm Contact"

stLinkCriteria = "[Engineer Name]=" & "'" & Me![Firm Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command620_Click:
Exit Sub

Err_Command620_Click:
MsgBox Err.Description
Resume Exit_Command620_Click

End Sub

Any help is appreciated... I'm a rooky with Access...

The problem is that you're using ' as a delimiter in your
stLinkCriteria, and it's seeing the apostrophe as a closing quote. Use
" instead; to insert a doublequote in a doublequote delimited string
double the doublequote:

stLinkCriteria = "[Engineer Name]=""" & Me![Firm Contact] & """"

This will give you a criterion like [Engineer Name] = "Kevin O'Toole"

Just a warning: names are NOT unique. What happens if you have two
engineers both of whom happen to be named Jim Smith? I'd suggest a
(concealed) unique EngineerID.

John W. Vinson[MVP]