Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Why does this SQL Statement generate an error?

Status
Not open for further replies.

MacDaddyNickP

Programmer
Joined
Jun 7, 2005
Messages
22
Location
US
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?
 
Presumably everything in the VALUES clause is a VB variable name. If you are doing something like
Code:
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
Code:
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
Code:
... ",#" & datSvcReqStatusDate & "#,'" & strCompany & "'," ...




[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
Exactly what I meant :rolleyes:
Sorry for the dumb post.

Did not notice your "insert into
 
Thanks Golom! I just couldn't see the trees for the forest.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top