• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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
0
TomBock2004
Asked:
TomBock2004
1 Solution
 
TimCotteeCommented:
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
 
LucasCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now