Combo Box Error-Name with ' like O'Brien

[Guest]

I created a combo box based on Name which is a field created by joining last name, first name as follows

Name: [Name-Last] & ", " & [Name-First

I am getting a Run-time error'3077' Syntax error (missing operator) in expression when I select a name with an ' like O'Brien. The combo box works fine for all other names.

The code created for this combo box is

Private Sub Combo56_AfterUpdate(
' Find the record that matches the control
Dim rs As Objec

Set rs = Me.Recordset.Clon
rs.FindFirst "[Name] = '" & Me![Combo56] & "'
If Not rs.EOF Then Me.Bookmark = rs.Bookmar
End Su

The error line is the rs.FindFirst "[Name] = '" & Me![Combo56] & "'

Any ideas on how to correct this?

 

[RSGinCA]

I just had the same problem yesterday, and posted it on a different board and
got an answer.

The solution is to replace 1 single quote with 2 single quotes in the text that
you are searching for. O'Brien becomes O''Brien. You can use the Replace
command to do it.

Here's modifications to your code:

Private Sub Combo56_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

Dim strTemp as String
strTemp = Replace(Me![Combo56, "'", "''")
rs.FindFirst "[Name] = '" & strTemp & "'"

X rs.FindFirst "[Name] = '" & Me![Combo56] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Actually it could probably all be done in one line (without the new string
variable):

rs.FindFirst "[Name] = '" & Replace(Me![Combo56, "'", "''") & "'"

Note: I can't guarantee this is all syntactically correct, but it should show
the concept.

Subject: Combo Box Error-Name with ' like O'Brien
From: "=?Utf-8?B?U2FuZHk=?=" (e-mail address removed)
Date: 2/28/2004 9:31 AM Pacific Standard Time
Message-id: <[email protected]>

I created a combo box based on Name which is a field created by joining last
name, first name as follows

Name: [Name-Last] & ", " & [Name-First]

I am getting a Run-time error'3077' Syntax error (missing operator) in
expression when I select a name with an ' like O'Brien. The combo box
works fine for all other names.

The code created for this combo box is

Private Sub Combo56_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![Combo56] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The error line is the rs.FindFirst "[Name] = '" & Me![Combo56] & "'"

Any ideas on how to correct this?

 

[Ken Snell]

The ' character is being interpreted by the Jet engine in the "WHERE"
statement as the end of the text string, and the "Brien" part after it
appears to be an error in syntax.

You need to double up the ' characters in the O'Brien text string in order
to have the embedded ' character be seen as a real ' character and not as a
delimiter. If you have ACCESS 2000 or higher, you can easily do this with
the Replace function:

rs.FindFirst "[Name] = '" & Replace(Me![Combo56], "'", "''", 1, -1,
vbTextCompare) & "'"


--
Ken Snell
<MS ACCESS MVP>

I created a combo box based on Name which is a field created by joining

last name, first name as follows

Name: [Name-Last] & ", " & [Name-First]

I am getting a Run-time error'3077' Syntax error (missing operator) in

expression when I select a name with an ' like O'Brien. The combo box
works fine for all other names.

The code created for this combo box is

Private Sub Combo56_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![Combo56] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The error line is the rs.FindFirst "[Name] = '" & Me![Combo56] & "'"

Any ideas on how to correct this?

 

[Cheryl Fischer]

Alternatively, you can use Chr(34), the character code for a double quote:

rs.FindFirst "[Name] = " & Chr(34) & Me![Combo56] & Chr(34)




--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I created a combo box based on Name which is a field created by joining

last name, first name as follows

Name: [Name-Last] & ", " & [Name-First]

I am getting a Run-time error'3077' Syntax error (missing operator) in

expression when I select a name with an ' like O'Brien. The combo box
works fine for all other names.

The code created for this combo box is

Private Sub Combo56_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![Combo56] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The error line is the rs.FindFirst "[Name] = '" & Me![Combo56] & "'"

Any ideas on how to correct this?

 

[Ken Snell]

Then the one that you're using as the delimiter must be doubled in the
string, and the other would not be.

--
Ken Snell
<MS ACCESS MVP>

What if you have ' and " as part of description. If I change code to

double quotes, I get error when " is part of description?