INSERT INTO syntax

[landeye]

I have two tables I am trying to insert data into. Both tables have an
Autonumber field (which I omitted from the INSERT INTO statement). I have
been successful in coding one of the forms to allow the INSERT INTO, but for
some reason I keep getting an "Invalid SQL Statement" on the other. I am
using the exact same syntax in both statements but the field types are
slightly different in the two tables.

Here's the syntax that works:
sqlPNote = "INSERT INTO DQPNote ( [NoteCo], [NoteProject], [NoteDate],
[NoteTaker], [Note], [Complete] ) " & _
" VALUES('" & tmpPNoteCo & "', '" & tmpPNoteProject & "', '" & tmpPNoteDate
& "', '" & tmpPNoteTaker & "', '" & tmpPNote & "', '" & tmpPNoteComplete &
"')"
.......NoteCo, NoteProject, NoteTaker, Note are all strings
.......NoteDate is a Datefield
.......Complete is a Boolean

Here's the syntax that doesn't:
sqlINote = "INSERT INTO DQINote ( [ARIDx], [NoteDate], [NoteTaker], [Note],
[Complete] ) " & _
" VALUES(" & tmpARIDx & ", '" & tmpINoteDate & "', '" & tmpINoteTaker & "',
'" & tmpINote & "', '" & tmpINoteComplete & "')"
.......ARIDx is a Number
.......NoteDate is a Datefield
.......NoteTaker, Note are Strings
.......Complete is Boolean

I appreciate any help. Thanks.

 

[Alex Dybenko]

Hi,
perhaps tmpARIDx is null?
anyway - run in debug window:
?sqlINote
and then look at resulting SQL again, also you can copy paste it into query
sql view and run it

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Douglas J. Steele]

I'm surprised that the first one works. If NoteDate is a Date field, the
value being passed needs to be delimited with # characters, and, regardless
of your regional settings, needs to be in mm/dd/yyyy format (or an
unambiguous format like yyyy-mm-dd or dd mmm yyyy).

Assuming tmpPNoteComplete is returning True or False, its value should not
be enclosed in quotes.

Finally, if any of the text has apostrophes in it, your code will fail due
to the use of ' as the text delimiter. (Of course, if you use " as the text
delimiter, then it will fail if any of the text has double quotes in it.

Try:

sqlINote = "INSERT INTO DQINote " & _
"( [ARIDx], [NoteDate], [NoteTaker], [Note], [Complete] ) " & _
" VALUES(" & tmpARIDx & ", " & _
Format(tmpINoteDate, "\#yyyy\-mm\-dd\#") & ", '" & _
Replace(tmpINoteTaker, "'", "''") & "', '" & _
Replace(tmpINote, "'", "''") & "', '" & _
Replace(tmpINoteComplete, "'", "''") & "')"

Exagerated for clarity, those three Replace statements are:

Replace(tmpINote, " ' ", " ' ' ")