Single quote error

[scottfarcus]

I am using some SQL statements to create and update records in my app.

If I enter a subject such as, "I'll be out of the office all week." into a record, an error is caused by the apostrophe in the word "I'll".

How do I avoid the error while keeping the ability to use apostrophe's in my title and subject lines?

Scott
access_junkie@hotmail.com

 

[Jack Cowley]

Use double quotation marks ("") around your string instead of the single quotation (") mark.

 

[scottfarcus]

strSQL = "UPDATE tblTaskDaily SET tblTaskDaily.HoursWorked = "
strSQL = strSQL & Me.txtActualHours
strSQL = strSQL & " , tblTaskDaily.EstimateHours = "
strSQL = strSQL & Me.txtEstimateHours
strSQL = strSQL & " , tblTaskDaily.Comment = "
strSQL = strSQL & "'" & Me.txtComment & "'"
strSQL = strSQL & " WHERE tblTaskDaily.StaffId = " & Me.txtStaffID & " AND tblTaskDaily.TaskID = " & Me.txtTaskID & " AND tblTaskDaily.ChargeDate = #" & Me.txtChargeDate & "#;"

This is the SQL statement I run.

It pulls values from a couple of fields on my form. As you know, strings passed in SQL must be enclosed in single quotes - aka apostrophes (').

If an apostrophe is included in the string (Ex: If me.txtComment = I'll be out of the office all week.) then an error is produced because SQL recognizes the apostrophe in the string as the end of the string.

Now, I need to be able to include apostrophes in my strings and still pass them through SQL statements.

Hope this clarifies the problem.

Any help is appreciated.

 

[Jack Cowley]

Try:
strSQL = strSQL & """ & Me.txtComment & """

 

[Ken Grubb]

Or Try:
Dim strQuote As String
strQuote = Chr$(34)
strSQL = strSQL & strQuote & Me.txtComment & strQuote

 

[scottfarcus]

I'm an idiot.

Works perfectly, thank you both.

Scott