Assign text to a text field

[itlearner]

I try to assign a building name to a text field named [Building Name]. If the name contains a single quotation (eg. Smith's Law Building), I will get an error message. The reason is that I already use a single quotation to mark a file as a text field. How I would fix this?

The statement is showed as below. lgBuilding ID is a variable. Fields(1) to Fields(4) are from other recordset.

DoCmd.RunSQL "INSERT INTO [Building] " & _
"([BuildingID], [Building Name], [Street No], [Street Name], [Street Type]) VALUES " & _
"(" & lgBuildingID & ", '" & .Fields(1) & "', " & .Fields(2) & ", '" & .Fields(3) & "', '" & .Fields(4) & "');"