One SQL statement works, other doesn't in VB6 App

[ambra19]

I have a Note box on a form that deals with Franchisees. It allows that I can add new notes for different call dates. Each Note has it's own id. These can only be added and not editted. I have a second form that has something similar however, these notes can be editted later. Here is where the problem is. I am unable to add a new note. My SQL for the editting/updating of a note works, however I receive the "Run-time error -2147217900(80040e14)':
Syntax Error in INSERT INTO statement"


The SQL for INSERT INTO statement for the Franchisee works correctly:

If varFranchNotes(0) = 0 Then
strSQL = "INSERT INTO tblFranchNotes (FNDate, Notes, Fid)" & _
"VALUES('" & _
varFranchNotes(1) & "','" & _
varFranchNotes(2) & "', " & _
varFranchNotes(3) & &quot"

End If

The one that doesn't work is as follows:
If varDateNotes(0) = 0 Then
strSQL = "INSERT INTO tblStoreOpenDate (OpenDate, Note, Sid)" & _
"VALUES('" & _
varDateNotes(1) & "','" & _
varDateNotes(2) & "'," & _
varDateNotes(3) & &quot"

Else
'create sql for updating record
strSQL = "UPDATE tblStoreOpenDate SET " & _
"tblStoreOpenDate.OpenDate = " & "'" & varDateNotes(1) & "', " & _
"tblStoreOpenDate.Note = " & "'" & varDateNotes(2) & "', " & _
"tblStoreOpenDate.Sid = " & varDateNotes(3) & _
" WHERE ODid = " & varDateNotes(0)
End If

As I mentioned before, the UPDATE works, however, for whatever reason, the INSERT INTO statement doesn't.
Any help would be greatly appreciated. I am going nutz trying to figure this out (I am not the greatest coder in the world).
Regards,
ambra19

 

[Transcend]

Have you tried putting a stop in your code to see what strSQL actually contains? You may find you've missed a comma or a bracket ...

Transcend

 

[ambra19]

Hi Transcend,
Thank you for your prompt response. I did check the strSQL and have included the results below.

I did, here is what I get for both:
1. The SQL statement that works from the Franchisee Form:
INSERT INTO tblFranchNotes (FNDate, Notes, Fid)VALUES('2003-03-19','Here is the functioning version', 9)


2. Here is the SQL statement that I get an error on:
INSERT INTO tblStoreOpenDate (OpenDate, Note, Sid)VALUES('2002-07-18','Here is the non-functioning', 1)

Everything seems to be fine. I can't figure out why these do not work.
Regards,
ambra19

 

[Transcend]

What kind of database are you using? Access? SQL server?
Do you have access to query analyser so that you can run the value of strSQL straight on the db?

Transcend

 

[ambra19]

Transcend,

Thanks for the idea. I tried it running as a query in Access. I guess that 'Note' as a column heading doesn't work on an INSERT STATEMENT for reasons I do not know. I will try this tomorrow and let you know how it works.
Thanks.
ambra19

 

[ambra19]

It worked.
Thanks again Transcend.
ambra19