Inconsistent error

bretdunlap

Registered User.
Local time
, 22:49
Joined
Nov 15, 2011
Messages
54
HI

I have a SQL error but it only happens occasionally I can't nail down when it will happens. The only consistent part, basically I have a way of sending good job notes to people, I type in the note then save it to a drop down if I never typed it before, then after sending it to ms publisher, I then hit the save button it gets date stamped with the persons code so I can see what was the last not I sent that person.

occasionally I get an error

3075 Syntax error (missing operator) in query expression "We Miss you class just isn't the same with out you',' Adults','Bouer','Jeff')'.

here is the code the problem is it works great but not with some of the drop downs as if the wording in the note is throwing off the code.

Private Sub CommandSaveGJN_Click()
Dim sSql As String

If Len(textGoodJobNote) = 0 Or IsNull(textGoodJobNote) Then
MsgBox "Please Enter a Note!!!"
Exit Sub
ElseIf Not IsNull(Me.Text50.Value) Or Len(Me.Text50.Value) = 0 Then

sSql = "INSERT INTO [GoodJobNotesQuery] (StudentID,GJNotes,Team,LastName,FirstName) VALUES ('" & Me!Text50 & "','" & Me!textGoodJobNote & "','" & Me!Text26 & "','" & Me!Text2 & "','" & Me!Text0 & "')"
CurrentDb.Execute sSql
textGoodJobNote = ""
ListGoodJob.Requery
End If

End Sub
 
We Miss you class just isn't the same with out you',' Adults','Bouer','Jeff')'

The single qoutes inside your strings are breaking up sSQL-string, you need to double up your quotes around any of the value fields that CAN contain a single qoute. like O'Brian.

ex:

""" & Me.text50 & """"

or use Chr(34) as a wrapper for more a more readable code.

Chr(34) & me.Text50 & Chr(34)

Here is one suggestion to your sSQL

Code:
sSQL = "INSERT INTO [GoodJobNotesQuery] " & _
       "(StudentID,GJNotes,Team,LastName,FirstName) " & _
       "VALUES ( " & _
       Chr(34) & Me!Text50 & Chr(34) & "," & _
       Chr(34) & Me!textGoodJobNote & Chr(34) & "," & _
       Chr(34) & Me!Text26 & Chr(34) & "," & _
       Chr(34) & Me!Text2 & Chr(34) & "," & _
       Chr(34) & Me!Text0 & Chr(34) & ")"

Hope this helps

JR
 
Thank you so much worked like a charm :):D
 
A better solution is to create a temporary QueryDef to execute, the you don't have to worry about dizzeing qoutes and date delimiters.

Code:
With CurrentDb.CreateQueryDef("", "INSERT INTO GoodJobNotesQuery " & _
                                  "(StudentID,GJNotes,Team,LastName,FirstName) " & _
                                  "Values (p0,p1,p2,p3,p4)")
    .Parameters("p0") = Me.Text50
    .Parameters("p1") = Me.textGoodJobNote
    .Parameters("p2") = Me.text26
    .Parameters("p3") = Me.Text2
    .Parameters("p4") = Me.Text0
    .Execute
    .Close
End With
 
It amazes me how many different ways there are to wright the same code.
 
It just happened again I am going to try the first solution then if it happens again I will try the third
 
Ok the "" sor of worked but I got "O'Brian" in my table instead of O'Brian I tried the other and so far that seams to work Thanks again for your time
 

Users who are viewing this thread

Back
Top Bottom