SQL Insert from Field Values, with quotes..

[Lord-Data]

I've got a form that uses a SQL insert statement to put the contents of its
fields into a MS Access 97 database ..
using the following statement:
CurrentDb.Execute " INSERT INTO Repairs ( ID, JobDate, Supplier,
SupplierAddress, Article, " _
& "ProductDescription, Quantity, RA, PO, Serial, Reason, " _
& "CustomerName, CustomerCompany, CustomerAddress, PhoneBH, " _
& "PhoneAH, Carrier, CarrierCon, DespatchDate, JobComments, " _
& "CustomerOwned, Waranty, Teammember, CompletedFlah) " _
& "VALUES (" & Form.ID.Value & ", " & Form.JobDate.Value & ", '" &
Form.Combo92.Value & "' , '" & Form.SupplierAddress.Value & "', '" &
Form.Article.Value & "', " _
& "'" & Form.ProDesc.Value & "', " & Form.QTY.Value & ", '" &
Form.RA.Value & "', " & Form.PO.Value & ", '" & Form.Serial.Value & "', '" &
Form.Reason.Value & "', " _
& "'" & Form.CustomerName.Value & "', '" & Form.CustomerCompany.Value &
"', '" & Form.CustomerAddress.Value & "', '" & Form.PhoneBH.Value & "', " _
& "'" & Form.PhoneAH.Value & "', '" & Form.Carrier.Value & "', '" &
Form.CarrierCon.Value & "', " & Form.DespatchDate.Value & ", '" &
Form.JobComments.Value & "', " _
& "" & Form.CustomerOwned.Value & ", " & Form.Repair.Value & ", '" &
Form.TeamMember.Value & "', No" _
& ");"

Excuse the size

This works flawlessly most of the time, except when there is a quote in the
fields to be entered ..
Is there a way around this? This final product will be used by non computer
literate people, so I can't just avoid using signal quotes.. they will have
to be handled .. How can I accomplish this?

Thanks in advance!

 

[Douglas J. Steele]

For any word that has a single quote in it, you need to replace that single
quote with two single quotes. While this is easy in Access 2000 and higher
(you simply use the built-in Replace function), it's a bit trickier in
Access 97 (you have to write your own equivalent for the Replace function)

Alternatively, if none of the words have double quotes in them, use a double
quote for your delimiter in the SQL rather than a single quote. The easiest
way to do that is to use Chr$(34) where you want the delimiter:

"VALUES (" & Form.ID.Value & ", " & Form.JobDate.Value & ", " & Chr$(34) &
Form.Combo92.Value & Chr$(34) & ", " & ...

I wrote about this topic in my May, 2004 Access Answer column for Pinnacle
Publication's Smart Access. You can get a copy of that column (and the
accompanying database, which is in Access 97) for free at
http://members.rogers.com/douglas.j.steele/SmartAccess.html