Syntax error in my query help

[tiger]

I am getting syntax error message from this Insert Into query...this is a
jet database. Accomplishment is a text type, ImsRef is a number type, [Key]
is a radio button, [Date] is a date type, ProgramIPT is a text type. This
different queries are all the same but I am getting error messages from them
all. All the values are coming from a textboxes on a form.

CommandText = "INSERT INTO tblAccomplishment(" & _
"Accomplishment, ImsRef, [Key], [Date],
ProgramIPT) "& _
"VALUES ("&chr$(34)&Accomp1 &chr$(34)& "," & _
Text36& "," &chr$(34)&Option88&chr$(34)& "," & _
Format(txtDate1, "\#mm\/dd\/yyyy\#")& "," & _
chr$(34)&txtIPT&chr$(34)& ")"

Thanks
Tiger

 

[David Lloyd]

Tiger:

One suggestion would be to use the Debug.Print method to print out the
CommandText variable in the Immediate Window. This will give you more
information about how the resulting CommandText string appears. You can
then copy and paste this string into the SQL query window and run it. The
query window will usually highlight the portion of the SQL statement where
the issue lies.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am getting syntax error message from this Insert Into query...this is a
jet database. Accomplishment is a text type, ImsRef is a number type, [Key]
is a radio button, [Date] is a date type, ProgramIPT is a text type. This
different queries are all the same but I am getting error messages from them
all. All the values are coming from a textboxes on a form.

CommandText = "INSERT INTO tblAccomplishment(" & _
"Accomplishment, ImsRef, [Key], [Date],
ProgramIPT) "& _
"VALUES ("&chr$(34)&Accomp1 &chr$(34)& "," & _
Text36& "," &chr$(34)&Option88&chr$(34)& "," & _
Format(txtDate1, "\#mm\/dd\/yyyy\#")& "," & _
chr$(34)&txtIPT&chr$(34)& ")"

Thanks
Tiger

 

[Christophe Niel]

Well,

chr$(34) is the way to get a " (double quote) character.

In SQL you don't want a double quote to mark the begining and endin of a
string value, but a single quote ( ' )

your "value" string should look more like that

"VALUES( '" & Accomp1 & "', '" & _
Text36 & "', '" &_
Format(txtDate1, "\#mm\/dd\/yyyy\#") & "', '" & _
txtIPT & "' )"

Notice the double quote and the single quote or copy paste the text in a
text editor with the courrier Font, you will see the difference in the
character.