What is wrong with this?

[GizmoT]

I am having a problem with the following piece of code, but only incertain circumstances. It gives an error message when the Surname starts O'xxxx (i.e. O'Halloran, O'Connor etc)


Code = Left(LeadTable![Surname], 6) & Right(LeadTable![HomeTel], 4)

Set CustTable = MyDb.OpenRecordset("tblCustomer", DB_OPEN_DYNASET)
FilterCriteria = "[Identifier]='" + Code + "'"
CustTable.Filter = FilterCriteria
Set CustTable = CustTable.OpenRecordset()

The error message is

"Syntax Error (missing Operator) in expression '[Identifier]='O'hall4567';'



I presume this is due to the ' apostrophe being used to set the filtercriteria.

Is there a way round this problem? Is there a way to produce the "Code" without the apostrophe - i.e. O'Halloran becomes Ohallo?


The code gives each record a (almost) unique identifier, and searches the database to make sure that it has not been previously received.

 

[Mile-O]

FilterCriteria = "[Identifier] = """ & Code & """"

 

[KenHigg]

You can use "" to insert a quote.

FilterCriteria = "[Identifier]=""" + Code + """"


Maybe this would work?

 

[Mile-O]

FilterCriteria = "[Identifier]=""" + Code + """"

Don't forget that + is an arithmetical operator and, while it does work, is not the recommended operator. The ampersand is the recommended.

 

[GizmoT]

Aw - thanks guys. It works. You people rock!