Apostrophe Error in LastName

[Guest]

Hi all,
I have a form with a search box that lists the full names of Employees: -

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

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

This works fine when choosing 'Joe Bloggs' as it correctly returns the
correct record. However, I noticed this morning that choosing 'Geoffrey
O'Brien' results in the following error: -

Run-time error '3077':
Syntax error (missing operator) in expression.

It must be picking up on the ' (apostrophe) in his last name! Is there a way
around this?

Thanks.

 

[RoyVidar]

BetaMike wrote in message

Hi all,
I have a form with a search box that lists the full names of
Employees: -

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

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

This works fine when choosing 'Joe Bloggs' as it correctly returns
the correct record. However, I noticed this morning that choosing
'Geoffrey O'Brien' results in the following error: -

Run-time error '3077':
Syntax error (missing operator) in expression.

It must be picking up on the ' (apostrophe) in his last name! Is
there a way around this?

Thanks.

Wouldn't you be using a primary key field for such in stead of a long
string?

Anyway, here's a couple of ways of dealing with apostrophes

rs.FindFirst "[FullName]= '" & replace(Me![Combo66], "'", "''") & "'"

- replace any single occurance of it, with two

rs.FindFirst "[FullName]= """ & Me![Combo66] & """"

- doublequoting

 

[Allen Browne]

If you don't understand the double-quotes that Roy suggested, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

BetaMike wrote in message

Hi all,
I have a form with a search box that lists the full names of Employees: -

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

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

This works fine when choosing 'Joe Bloggs' as it correctly returns the
correct record. However, I noticed this morning that choosing 'Geoffrey
O'Brien' results in the following error: -

Run-time error '3077':
Syntax error (missing operator) in expression.

It must be picking up on the ' (apostrophe) in his last name! Is there a
way around this?

Thanks.

Wouldn't you be using a primary key field for such in stead of a long
string?

Anyway, here's a couple of ways of dealing with apostrophes

rs.FindFirst "[FullName]= '" & replace(Me![Combo66], "'", "''") & "'"

- replace any single occurance of it, with two

rs.FindFirst "[FullName]= """ & Me![Combo66] & """"

- doublequoting

 

[Guest]

Roy, thanks for the fix and also thanks to Allen for the explanation

BetaMike wrote in message

Hi all,
I have a form with a search box that lists the full names of
Employees: -

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

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

This works fine when choosing 'Joe Bloggs' as it correctly returns
the correct record. However, I noticed this morning that choosing
'Geoffrey O'Brien' results in the following error: -

Run-time error '3077':
Syntax error (missing operator) in expression.

It must be picking up on the ' (apostrophe) in his last name! Is
there a way around this?

Thanks.

Wouldn't you be using a primary key field for such in stead of a long
string?

Anyway, here's a couple of ways of dealing with apostrophes

rs.FindFirst "[FullName]= '" & replace(Me![Combo66], "'", "''") & "'"

- replace any single occurance of it, with two

rs.FindFirst "[FullName]= """ & Me![Combo66] & """"

- doublequoting