Bobby Heid
bheid at appdevgrp.com
Fri May 2 11:51:36 CDT 2003
I use this: 'add single quotes to beginning/end of a string Public Function AddQuote(vData As Variant) As String If IsNull(vData) Then AddQuote = "NULL" Else AddQuote = "'" & SQLText(CStr(vData)) & "'" End If End Function Public Function SQLText(lstrIn As String) As String Dim intStringLocation As Integer Dim strLeft As String Dim strRight As String intStringLocation = 0 intStringLocation = InStr(intStringLocation + 1, lstrIn, "'") Do While intStringLocation > 0 strLeft = Left(lstrIn, intStringLocation - 1) strRight = Mid(lstrIn, intStringLocation + 1) lstrIn = strLeft & "''" & strRight intStringLocation = intStringLocation + 2 intStringLocation = InStr(intStringLocation, lstrIn, "'") Loop SQLText = lstrIn End Function -------------------------------------------------- call with: strSQL = "INSERT INTO tblEmployeeMonthlyTask ([TaskDescription]) VALUES " strSQL = strSQL & "(" & AddQuote(Request.Form("txtTaskDescription")) & ")" I make no claims for this code as it is kind of messy and I did not write all of it. LOL. But it does work. It can be optimized somewhat also. HTH, Bobby -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Michael Tucker Sent: Friday, May 02, 2003 11:47 AM To: accessd at databaseadvisors.com Subject: [AccessD] Function for "scrubbing" strings before inserting them into SQLstatements? Hi everyone, I'm sure everybody has faced this situation, but I can't seem to figure out an elegant solution. I have a webpage interface to an Access database where users can enter notes on various things. Of course the user may enter apostrophes or quotation marks in these notes, but of course that sends my SQL statement into a tizzy. A sample of my SQL statement... strSQL = "INSERT INTO tblEmployeeMonthlyTask ([TaskDescription]) VALUES " strSQL = strSQL & "('" & Request.Form("txtTaskDescription") & "')" Is there a function that will convert my string so that it usable inside of this SQL statement even if it contains apostrophes and quotes? Thank you. _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com