Run-Time Error 3061

[bullfrog83]

I'm getting "Error 3061: To few parameters. Expected 2." in the CurrentDb.Execu te lines in the following code:

'variables previously defined
'rstCust previously defined
'rstStdCourses previously defined

Do While Not rstStdCourses.EOF
  varTakenCourseId = rstCust!TakenCourseId
  blnWaived = rstCust!Waived
  lngStdDegReqCourseId = rstStdCourses!StdDegReqCourseId
  'If some criteria is met Then
    CurrentDb.Execute "UPDATE StdDegReqCourse SET TakenCourseId = varTakenCourseId, Waived = blnWaived, Customized = -1 WHERE StdDegReqCourseId = " & lngStdDegReqCourseId
    Exit Do
  Else
  End If
  rstStdCourses.MoveNext
Loop

The values for the variables are as follows:

varTakenCourseI d = Null (variable datatype as I need it to accept nulls)
blnWaived = True
lngStdDegReqCou rseId = 9122

I am unable to determine where the problem lies.

[Dan2kx]

Could it be an error in your SQL?

It appears to me that you havent put your variables in correctly...

Specifically blnWaived and varTakenCourseI D
You need to wrap them in " & VARIABLE & " depending on there data type....
eg '" & STRING & "' OR " & LONG/INTEGER & " OR #" & DATE & "#
(you dont need the ' for Null or True)

eg....

"UPDATE StdDegReqCourse SET TakenCourseId = " & varTakenCourseId & ", Waived = " & blnWaived & ", Customized = -1 WHERE StdDegReqCourseId = " & lngStdDegReqCourseId

You could also try to debug.print this SQL to see what you the string actually constructs and try running the SQL in query builder.

Hope it works, I not an expert tho (just a bit bored (LOL))

Dan

[bullfrog83]

Could it be an error in your SQL?

It appears to me that you havent put your variables in correctly...

Specifically blnWaived and varTakenCourseI D
You need to wrap them in " & VARIABLE & " depending on there data type....
eg '" & STRING & "' OR " & LONG/INTEGER & " OR #" & DATE & "#
(you dont need the ' for Null or True)

eg....

"UPDATE StdDegReqCourse SET TakenCourseId = " & varTakenCourseId & ", Waived = " & blnWaived & ", Customized = -1 WHERE StdDegReqCourseId = " & lngStdDegReqCourseId

You could also try to debug.print this SQL to see what you the string actually constructs and try running the SQL in query builder.

Hope it works, I not an expert tho (just a bit bored (LOL))

Dan

Duh! I knew it was something simple. Thanks! However, you said that if a value is Null I don't need the '. However, if varTakenCourseI d is Null, using the debug.print that you recommended, this is what is being constructed:

UPDATE StdDegReqCourse SET TakenCourseId = , Waived = True, Customized = -1 WHERE StdDegReqCourseId = 9622

It appears to be rendering the Null as a blank. In order to not do that I had to add the extra apostrophe as such: '" & varTakenCourseI d & "' I didn't need it for the blnWaived variable, though. Any thoughts?

[Dan2kx]

If your data type for varTakenCourseI D is set to variant then you are correct, it will not print the NULL value in the SQL (my mistake, wasn't thinking), im my test it does not print it with the single quotes either....

what you actually need to do in this situation would be this

" & nz(varTakenCourseID, "Is Null") & "

You can search help for an accurate description of the nz() function but basically it turns a null value into whatever you want... if it is null of course.

This would then supply the "null" in the correct format for the SQL if the value is null (which is: "Is Null" (without quotes)), i assume that this variable is normally a number so this would also satisfy the number format also.

Hope this makes sense

Dan

[patjones]

I think you are almost there, but "Is Null" won't work in the Nz function call. I would say to write it as Nz(varTakenCour seID, Null), however it may or may not work depending on the properties of the column TakenCourseId.

What is the data type of TakenCourseId?

Pat

[bullfrog83]

If your data type for varTakenCourseI D is set to variant then you are correct, it will not print the NULL value in the SQL (my mistake, wasn't thinking), im my test it does not print it with the single quotes either....

what you actually need to do in this situation would be this

" & nz(varTakenCourseID, "Is Null") & "

You can search help for an accurate description of the nz() function but basically it turns a null value into whatever you want... if it is null of course.

This would then supply the "null" in the correct format for the SQL if the value is null (which is: "Is Null" (without quotes)), i assume that this variable is normally a number so this would also satisfy the number format also.

Hope this makes sense

Dan

That works! I had to edit it though to be Nz(varTakenCour seId,Null). Thank you very much! You have been very helpful!

[Dan2kx]

I think you are almost there, but "Is Null" won't work in the Nz function call. I would say to write it as Nz(varTakenCour seID, Null), however it may or may not work depending on the properties of the column TakenCourseId.

What is the data type of TakenCourseId?

Pat

Like you say, it depends on the Datatype, i assumed it was Variant.

Glad its solved for ya BullFrog

Dan

[patjones]

That works! I had to edit it though to be Nz(varTakenCour seId,Null). Thank you very much! You have been very helpful!

I should clarify what I just wrote. As Dan2kx wrote the Nz function call, it will work in the sense that it will insert the string "Is Null" in the column. However if you want the entry to be truly null, you need to specify the Null keyword in the Nz function call.

Pat

[bullfrog83]

I think you are almost there, but "Is Null" won't work in the Nz function call. I would say to write it as Nz(varTakenCour seID, Null), however it may or may not work depending on the properties of the column TakenCourseId.

What is the data type of TakenCourseId?

Pat

The datatype for TakenCourseId is Number (Long Integer) at the table level but I made the variable in my proc a variant so it can handle nulls since Long can't.

[bullfrog83]

I should clarify what I just wrote. As Dan2kx wrote the Nz function call, it will work in the sense that it will insert the string "Is Null" in the column. However if you want the entry to be truly null, you need to specify the Null keyword in the Nz function call.

Pat

Thanks! I've used the Nz function before but forgot about it so when Dan suggested it, I knew I had to have Null instead of Is Null.