Why does this SQL Statement generate an error?

[MacDaddyNickP]

I have a dynamic SQL statement which appends a record to a local Access table. This is the statement:

INSERT INTO tbl_Scoring_Results (DPS_Num, Svc_Request_Status_Date, Company, City, State, Overall_Score, Communication_Score, Professionalism_Score, Expertise_Score, On_Time_Score, Dell_Score) VALUES (strDPS_NUM, datSvcReqStatusDate, strCompany, strCity, strState, intOverall, intCommunication, intExpertise, intProfessionalism, intOnTime, intDell);

When this executes, I get an error:

3601 Too Few Parameters (expected 11)

What's going on?

 

[ItIsHardToProgram]

Where is your FROM

 

[Golom]

Presumably everything in the VALUES clause is a VB variable name. If you are doing something like

Dim SQL As String
SQL = INSERT INTO tbl_Scoring_Results (DPS_Num, Svc_Request_Status_Date, Company, City, State, Overall_Score, Communication_Score, Professionalism_Score, Expertise_Score, On_Time_Score, Dell_Score) VALUES (strDPS_NUM, datSvcReqStatusDate, strCompany, strCity, strState, intOverall, intCommunication, intExpertise, intProfessionalism, intOnTime, intDell);"
Currentdb.Execute SQL

then it is interpreting the stuff inside VALUES as a character string rather than substituting the corresponding variable values. You need something like

Dim SQL As String
SQL = INSERT INTO tbl_Scoring_Results (DPS_Num, Svc_Request_Status_Date, Company, City, State, Overall_Score, Communication_Score, Professionalism_Score, Expertise_Score, On_Time_Score, Dell_Score) VALUES (" & strDPS_NUM & "," & datSvcReqStatusDate & "," & strCompany & "," & strCity & "," & strState & "," & intOverall & "," & intCommunication & "," & intExpertise & "," & intProfessionalism & "," & intOnTime & "," & intDell & ");"
Currentdb.Execute SQL

If the values are dates or text then you also need to enclose them in the appropriate delimiters

... ",#" & datSvcReqStatusDate & "#,'" & strCompany & "'," ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

Exactly what I meant
Sorry for the dumb post.

Did not notice your "insert into

 

[MacDaddyNickP]

Thanks Golom! I just couldn't see the trees for the forest.