Run-time error 3134

[Wheat]

I'm getting a run-time error 3134 which states there is a
problem with my syntax in the following code. I can't
seem to locate the error:

strSQL = "INSERT INTO " & _
"" & tblTest & "" & _
" (" & _
"" & BPID# & ", " & _
"" & Acct# & ", " & _
"" & CSR & ", " & _
"" & QA# & ", " & _
"" & Score & ", " & _
"" & Date & "" & _
") VALUES (" & _
"'" & intBPID & "'," & _
"'" & strAcct & "'," & _
"'" & strCSR & "'," & _
"'" & intQA & "'," & _
"'" & intScore & "'," & _
"'" & dtmDate & "');"

Any help is appreciated. Thanks.

 

[Douglas J. Steele]

Assuming tblTest and the field names in it are literals and not variables,
try:

strSQL = "INSERT INTO " & _
"tblTest" & _
" (" & _
"BPID#, " & _
"Acct#, " & _
"CSR, " & _
"QA#, " & _
"Score, " & _
"Date" & _
") VALUES (" & _
intBPID & "," & _
"'" & strAcct & "'," & _
"'" & strCSR & "'," & _
intQA & "," & _
intScore & "," & _
Format(dtmDate, "\#mm\/dd\/yyyy\#") & ")"

Numerical values (intBPID, intQA and intScore) do not need any delimiter,
and dates need # delimiters (plus must be in mm/dd/yyyy format, regardless
of what your Regional Settings may be)

(Okay, so it's not mandatory that the dates be in mm/dd/yyyy. What is
mandatory, if you don't use that format, is that the date must be
unambigous. dd mmm yyyy or yyyy-mm-dd will work dd/mm/yyyy won't work for
the first 12 days of each month.)

 

[Wheat]

Doug,

Thanks for the response, but I'm getting the same error
message. For the life of me I can't find the syntax
error. After looking at the same code for a while,
everything is starting to run together! I may take a
break and come back to it later.

 

[Van T. Dinh]

I am not sure as I avoid all the hash (#) signs and special symbols in
Field names / object names.

In your case, try enclose all Field names in square brackets like:

strSQL = "INSERT INTO " & _
" tblTest " & _
" ( " & _
" [BPID#], " & _
" [Acct#], " & _
" [CSR], " & _
" [QA#], " & _
" [Score], " & _
" [Date] " & _
" ) VALUES ( " & _
intBPID & ", " & _
" '" & strAcct & "', " & _
" '" & strCSR & "', " & _
intQA & ", " & _
intScore & ", " & _
Format(dtmDate, "\#mm\/dd\/yyyy\#") & " )"

"Date" is not a good Field name either since there is an in-built function
Date() and it can get very confusing later.

 

[Van]

Thanks Van! It works now. I only used the name Date as a
quick test to make sure the function would work. I going
to rebuild the table with the correct names.

Thanks again to all that helped!

-----Original Message-----
I am not sure as I avoid all the hash (#) signs and special symbols in
Field names / object names.

In your case, try enclose all Field names in square brackets like:

strSQL = "INSERT INTO " & _
" tblTest " & _
" ( " & _
" [BPID#], " & _
" [Acct#], " & _
" [CSR], " & _
" [QA#], " & _
" [Score], " & _
" [Date] " & _
" ) VALUES ( " & _
intBPID & ", " & _
" '" & strAcct & "', " & _
" '" & strCSR & "', " & _
intQA & ", " & _
intScore & ", " & _
Format(dtmDate, "\#mm\/dd\/yyyy\#") & " )"

"Date" is not a good Field name either since there is an in-built function
Date() and it can get very confusing later.

--
HTH
Van T. Dinh
MVP (Access)

Doug,

Thanks for the response, but I'm getting the same error
message. For the life of me I can't find the syntax
error. After looking at the same code for a while,
everything is starting to run together! I may take a
break and come back to it later.

.