Single apostrophe throws syntax error in textfield (single form view)

In a form, I'm using the "Form_BeforeUpdate" event to update a "history table" before changes are made to the record.  I'm using the strSQL command as shown below (***s)

**************
strSQL = "Insert into tbl02PrioritizationDataHistory (TrackingNumber, Division, Manager, ManagerCode) " & _
"VALUES ('" & Me!TrackingNumber & "', '" & Me!Division & "', '" & Me!Manager & "', '" & Me!ManagerCode & "')"

CurrentDb().Execute strSQL, dbFailOnError
**************


Note: With the exception of TrackingNumber, the other 3 fields (Division, Manager, ManagerCode) are all text fields.



This process works fine... However, I realize that entering a >>> ' <<< somewhere in the text field (e.g. "This year's budget") will throw a syntax error.

The exact error reads: Run-Time Error 3075.  Syntax error (Missing Operator) in query expression....


I understand as to why this error is caused... unfortunately, I'm not sure how to fix it.  Is there a chance to still use the single apostrophe in the textfield w/o throwing the syntax error?   If not, is there an alternative solution?  I'd rather not make the >>> ' <<< an "invalid" character.... users will want to use that.


Thanks,
Tom
TomBock2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
TimCotteeHead of Software ServicesCommented:
Hi TomBock2004,

You need to double the single quotes:

strSQL = "Insert into tbl02PrioritizationDataHistory (TrackingNumber, Division, Manager, ManagerCode) " & _
"VALUES ('" & Me!TrackingNumber & "', '" & FixUp(Me!Division) & "', '" & FixUp(Me!Manager) & "', '" & FixUp(Me!ManagerCode) & "')"

Where Fixup is:

Public Function FixUp(ByVal Something As String) As String
    FixUp = Replace(Replace(Something,"'","''"),"""","""""")
End Function

This will double any ' or " characters which means that they will be stored as single ' or " characters in the table.

Tim Cottee
0
 
TomBock2004Author Commented:
Tim:

Your suggestion sounds very promising... I tried it a moment ago; but unfortunately, I'm still getting the same error.

Here's what I've done:
- added the "Public Function FixUp" (verbatim) to the code
- changed SQL (textfields only).  E.g. '" & Me!Division & "', with '" & FixUp(Me!Division) & "',

Did I miss something?


Tom
0
 
LucasMS Dynamics DeveloperCommented:
You can use this function as well.  I will scan through your word and fix your apostrophe problem.

Public Function TextWithQuotes(theText As String) As String
  'checks if user used quotes
Dim result As String
Dim i As Integer
Dim ch As String

If IsNull(theText) Then Exit Function
       For i = 1 To Len(theText)
        ch = mID(theText, i, 1)
         If ch = "'" Then ch = "''"
         result = result & ch
     Next i
     TextWithQuotes = result
End Function

To use it:  TextWithQuotes('This year's budget')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above experts are correct.  SQL can't tell the difference between your terminating a string with a single-quote mark and a single quote mark...
0
 
miqrogrooveCommented:
This is a  very common problem in programming websites.  Languages such as PHP come with specialized functions for escaping variables that might have special characters in them.

I suggest the simplest method is to use the Replace() function to make your own specialzed escaping function.

For Example:

Enclose variable in double quotes so that apostrophes do not cause problems.  Then, escape any double quotes inside the variable like so:
Replace(myVariable, """", """""")

This is probably hard to read, but I think it is also equivalent to:

Replace(myVariable, '"', '""')


Enjoy,
-- Miqro
0
 
stevbeCommented:
a little known but useful function that is built-in ... BuildCriteria ... try it out in the immediate window

strTest = "my name's"

?BuildCriteria("myField", 1, strTest)

Steve
0
 
TomBock2004Author Commented:
Thanks for all of your comments... unfortunately, I'm still no further this... and it's probably my lack of knowledge...

Okay, whatever specialized function is called, the user doesn't care when and how it's called.   He/she simply wants to use the single apostrophe in either one of the 3 textboxes (which is a reasonable request).

So, w/o changing my current design too much, how can I bypass the problem and adjust my SQL code.  Again, Tim's suggestion seemed very promising.   Unfortunately, it still threw the same error.

How do I modify my SQL code to taken either one's recommendation into account?

Thanks again,
Tom
0
 
Jim P.Commented:
Put this in a module:

Public Function ReplSnglQuote(InputVal As String) As String

If InStr(1, InputVal, Chr(39)) = 0 Then
    ReplSnglQuote = InputVal
Else
    Do Until InStr(1, InputVal, Chr(39)) = 0
        InputVal = Chr(34) & Left(InputVal, InStr(1, InputVal, Chr(39)) - 1) & Chr(34) & _
                " & chr(39) & " & Chr(34) & _
                   Mid(InputVal, InStr(1, InputVal, Chr(39)) + 1, 1000) & Chr(34)
    Loop
    ReplSnglQuote = InputVal
End If

And then it will return
?ReplSnglQuote("tommy's workshop")
"tommy" & chr(39) & "s workshop"
Which reads to the system as
tommy's workshop




End Function
0
 
TomBock2004Author Commented:
jimpen:

Thanks for the prompt feedback... allow me to recap:

- simply put your function (verbatim) into a module
- then, users can enter e.g. "Organization's revenue" into either one of the 3 textfields.
- system will automatically change the SQL so that it'll be accepted by the execution command "CurrentDb().Execute strSQL, dbFailOnError"

Is this really that simple?

Tom
0
 
Jim P.Commented:
Oops. The End Function should be right after the end if.

Edited version:
Public Function ReplSnglQuote(InputVal As String) As String

If InStr(1, InputVal, Chr(39)) = 0 Then
    ReplSnglQuote = Chr(34) & InputVal & Chr(34)
Else
    Do Until InStr(1, InputVal, Chr(39)) = 0
        InputVal = Chr(34) & Left(InputVal, InStr(1, InputVal, Chr(39)) - 1) & Chr(34) & _
                " & chr(39) & " & Chr(34) & _
                   Mid(InputVal, InStr(1, InputVal, Chr(39)) + 1, 1000) & Chr(34)
    Loop
    ReplSnglQuote = InputVal
End If

End Function

Note that with this method you can just do the commas such as
strSQL = "Insert into tbl02PrioritizationDataHistory (TrackingNumber, Division, Manager, ManagerCode) " & _
"VALUES (" & Me!TrackingNumber & "," & ReplSnglQuote(Me!Division) & "," & ReplSnglQuote(Me!Manager) & "," & ReplSnglQuote(Me!ManagerCode) & ")"
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Jim P.Commented:
I might be wrong on deleting the single quotes, but the second function feeds back
from the debug window:
?ReplSnglQuote("Organization's revenue")
"Organization" & chr(39) & "s revenue"

So you will probably need the single quotes as in the original query, but it should interpret the chr(39) as single quote mark when it concantenates it into the string.

I've had to do something similar with ampersands before.
0
 
TomBock2004Author Commented:
Thanks, Jim.

This works great.  I appreciate your feedback on this.

tom
0
 
Jim P.Commented:
Your welcome and have a good day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.