SQL giving errors, but no reason why.... 1

[snayjay]

I'm running an UPDATE SQL statement and sometimes it works and sometimes it doesn't. I can't figure out why.

addbulletsql = "INSERT INTO BULLETtbl " _ & "(SSN,BULLETSTART,BULLET,NOTES) "_
& " VALUES ('"_
& Forms!PERSONNELfrm!SSN.Value & "', '"_
& bulletx & "', '"_
& ADDBULLET.Value & "', '"_
& ADDNOTES.Value & "')"

Now I've broken up the SQL statement here just because I hate reading examples that auto-wrap here because you can't just keep scrolling to the right. But on my program the SQL is on one line.

The code works so I don't think it's the code.

Fields being entered are:
1) SSN - being pulled from another form...I can assure you the information is there...because if it wasn't then the form I'm working on, would not have even displayed.
2) BULLETSTART - being pulled from a TEXTBOX on this form called ADDBULLET. This is only the first 45 chars of the TEXTBOX (assuming there are 45 chars) followed by "..."
3) BULLET - being pulled from the same TEXTBOX on this form called ADDBULLET
4) NOTES - being pulled from another TEXTBOX on this form called ADDNOTES

The code is activated by an OnClick Event of an Button called ADDcmd

So anyway, normally I would enter a complete sentence in the ADDBULLET textbox and then just haphazard notes in the ADDNOTES textbox. While testing I was just using things like Testing 1,2,3,4 in both fields. Worked like a champ. But then I tried actually putting something like what would actually be entered. I got the error:

Run-Time Error 3075: Syntax error(missing operator) in query expression "whatever I typed in the two textboxes"

So I went to debug and didn't see anything wrong with my code, so I went back to the form and erased what I had put and reinput Testing 1,2,3,4 and it took. So I thought it was a length problem in my TEXTBOX. So I checked and it was a Memo field so there shouldn't be a problem with 200 chars being passed from the textbox.

I'm so lost on this and really need to figure it out and keep it from erroring out.

Any help would be greatly appreciated.

~Snay

 

[snayjay]

In case someone thinks they need to see exactly how it's written on my form:

"INSERT INTO BULLETtbl " & "(SSN,BULLETSTART,BULLET,NOTES) " & " VALUES ('" & Forms!PERSONNELfrm!SSN.Value & "', '" & bulletx & "', '" & ADDBULLET.Value & "', '" & ADDNOTES.Value & "')

 

[PHV]

You may try this:
"INSERT INTO BULLETtbl " & "(SSN,BULLETSTART,BULLET,NOTES) " & " VALUES ('" & Forms!PERSONNELfrm!SSN.Value & "', '" & Replace(bulletx, [tt]"'", "''"[/tt]) & "', '" & Replace(ADDBULLET.Value, [tt]"'", "''"[/tt]) & "', '" & Replace(ADDNOTES.Value, [tt]"'", "''"[/tt]) & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snayjay]

I'm going to put that in and test it several times because like I said before I have no clue why it was happening because it didn't happen all the time. I'll get back with you shortly. But out of curiosity why add REPLACE to the last 3 but not the first value. Just curious in case I come up with this problem again. Thanks!

 

[snayjay]

It seems to have actually worked...I tried several different sentences in both textboxes (even similar to what got errors before) and it seems to work fine. Thanks for the help on that. Star for ya! But can you explain the REPLACE operator, the explanation in the VBA Code HELP wasn't detailed enough.

~Snay

 

[traingamer]

In this case, REPLACE is replacing any instance of a single quote with two single quotes. PH, being psychic, figured out that your data had single quotes in it.
(IF you delimit your data with single quotes, you have to double up imbedded single quotes for it to work. Otherwise, SQL doesn't know where YOUR input ends.)

traingamer

 

[snayjay]

Thanks for the explanation... I was mislead then because I read somewhere here before that I have to delimit Text with ' and Dates with # and no delimiting with Numbers. So should I change all my code to match this "REPLACE" method even though it currently works in other areas of my program with just a ' ?