Runtime Error 3061: Too few parameters. Expected 1.

Question

I am trying to run the following queris in VBA;

For intCounter = 1 To 16

strSQL = "INSERT INTO tbl_Temp_Hospitalizations (SAC_ID, ADMITTED_DATE, HOSPITAL_ID, DISCHARGED_DATE, DIAGNOSIS_ID, COMMENTS) SELECT SAC_ID, ADMIT, SITE, DISCHARGE, DIAG" & intCounter & ", DIAG_DESC" & intCounter & " FROM tbl_Hospitalization_Inpatient_Import WHERE DIAG" & intCounter & " Is Not Null"

dbs.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_Hospitalization_Inpatient_Import INNER JOIN tbl_Temp_Hospitalizations ON (tbl_Hospitalization_Inpatient_Import.SAC_ID = tbl_Temp_Hospitalizations.SAC_ID) AND (tbl_Hospitalization_Inpatient_Import.ADMIT = tbl_Temp_Hospitalizations.ADMITTED_DATE) AND (tbl_Hospitalization_Inpatient_Import.DISCHARGE = tbl_Temp_Hospitalizations.DISCHARGED_DATE) AND (tbl_Hospitalization_Inpatient_Import.SITE = tbl_Temp_Hospitalizations.HOSPITAL_ID) SET tbl_Temp_Hospitalizations.RESPONSIBLE_DX = " & intCounter & ", tbl_Temp_Hospitalizations.DIAGNOSIS_CLASS = " & tempDiagClass & " WHERE (((tbl_Temp_Hospitalizations.DIAGNOSIS_ID)=[DIAG" & intCounter & "]));"

dbs.Execute strSQL, dbFailOnError

Next intCounter

The error occurs in the second query. I am confused because when I get the SQL statement using Debug.Print.strSQL and plug it into a normal query (ie in SQL view) it works perfectly!

I would appreciate any help you could give me.

Answer 1

strSQL = "UPDATE tbl_Hospitalization_Inpatient_Import INNER JOIN tbl_Temp_Hospitalizations ON (tbl_Hospitalization_Inpatient_Import.SAC_ID = tbl_Temp_Hospitalizations.SAC_ID) AND (tbl_Hospitalization_Inpatient_Import.ADMIT = tbl_Temp_Hospitalizations.ADMITTED_DATE) AND (tbl_Hospitalization_Inpatient_Import.DISCHARGE = tbl_Temp_Hospitalizations.DISCHARGED_DATE) AND (tbl_Hospitalization_Inpatient_Import.SITE = tbl_Temp_Hospitalizations.HOSPITAL_ID)

SET tbl_Temp_Hospitalizations.RESPONSIBLE_DX = " & intCounter &  _

", tbl_Temp_Hospitalizations.DIAGNOSIS_CLASS = " & tempDiagClass & _

" WHERE (((tbl_Temp_Hospitalizations.DIAGNOSIS_ID)=[DIAG" & intCounter & "]));"

I would suspect that the problem is in the SET Clause. 

Is RESPONSIBLE _DX a number field.  You are setting its value as 1 to 16

Is DIAGNOSIS_CLASS a text field?  If so, what is the value of tempDiagClass?  If it is a string value then you need to add quotes around it.

", tbl_Temp_Hospitalizations.DIAGNOSIS_CLASS = """ & tempDiagClass & """" _

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 2

Hi Roger,

Thanks for the response. I took out the square brackets to see what would happen and it didn't seem to affect the query at all. Thanks for your help.

Answer 3

You are correct...I missed the quotes around the tempDiagClass string! It works great! Thanks so much John

Answer 4

I am trying to run the following queris in VBA;

For intCounter = 1 To 16

strSQL = "INSERT INTO tbl_Temp_Hospitalizations (SAC_ID, ADMITTED_DATE, HOSPITAL_ID, DISCHARGED_DATE, DIAGNOSIS_ID, COMMENTS) SELECT SAC_ID, ADMIT, SITE, DISCHARGE, DIAG" & intCounter & ", DIAG_DESC" & intCounter & " FROM tbl_Hospitalization_Inpatient_Import WHERE DIAG" & intCounter & " Is Not Null"

dbs.Execute strSQL, dbFailOnError

strSQL = "UPDATE tbl_Hospitalization_Inpatient_Import INNER JOIN tbl_Temp_Hospitalizations ON (tbl_Hospitalization_Inpatient_Import.SAC_ID = tbl_Temp_Hospitalizations.SAC_ID) AND (tbl_Hospitalization_Inpatient_Import.ADMIT = tbl_Temp_Hospitalizations.ADMITTED_DATE) AND (tbl_Hospitalization_Inpatient_Import.DISCHARGE = tbl_Temp_Hospitalizations.DISCHARGED_DATE) AND (tbl_Hospitalization_Inpatient_Import.SITE = tbl_Temp_Hospitalizations.HOSPITAL_ID) SET tbl_Temp_Hospitalizations.RESPONSIBLE_DX = " & intCounter & ", tbl_Temp_Hospitalizations.DIAGNOSIS_CLASS = " & tempDiagClass & " WHERE (((tbl_Temp_Hospitalizations.DIAGNOSIS_ID)=[DIAG" & intCounter & "]));"

dbs.Execute strSQL, dbFailOnError

Next intCounter

The error occurs in the second query. I am confused because when I get the SQL statement using Debug.Print.strSQL and plug it into a normal query (ie in SQL view) it works perfectly!

I would appreciate any help you could give me.

Wild shot, but what happens if you take the square brackets from around [DIAG" & intCounter & "] in the second SQL statement?

tempDiagClass & " WHERE (((tbl_Temp_Hospitalizations.DIAGNOSIS_ID)=DIAG" & intCounter & "));"

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com