Too few parameters, expected 1 Error

[aziz rasul]

I have the following SQL code which gives me run time error 3061 - Too few parameters, expected 1. Any ideas what I'm doing wrong? If I place a value like #01/11/12# in place of Me.Calendar9.Value then it works OK.

strSQL = "UPDATE tblAppealCases SET tblAppealCases.[ModRequestSMEAdjudicationDeadline] = DateAdd('d',14,Me.Calendar9.Value) WHERE (((tblAppealCases.[LetterReference])=" & strLetterReference & "));"

 

[PeterF]

Because you're running a SQL string in VBA functions will not be evaluated by referencing to a control (Me.Calendar9). The control name is a string so DateAdd wil not function, try:

strSQL = "UPDATE tblAppealCases SET tblAppealCases.[ModRequestSMEAdjudicationDeadline] = [COLOR="Red"]#" & DateAdd('d',14,Me.Calendar9.Value) & "#[/COLOR] WHERE (((tblAppealCases.[LetterReference])=" & strLetterReference & "));"

 

[Khalid_Afridi]

Mr. Peter is right. any SQL statement will just work as string for any query. The better way to any string statement in VBA is to create a query in the QBE and get its SQL in the editor. You have to take care of the parenthesis and braces in the VBA code editor to call any query correctly.

 

[boblarson]

Mr. Peter is right. any SQL statement will just work as string for any query. The better way to any string statement in VBA is to create a query in the QBE and get its SQL in the editor. You have to take care of the parenthesis and braces in the VBA code editor to call any query correctly.

You should still concatenate in any functions or form references when working with a SQL string in VBA so that the value is passed and not the reference thereby saving the database engine from having to try to evaluate the expression.

 

[aziz rasul]

Thanks all, that worked.

 

[Khalid_Afridi]

You should still concatenate in any functions or form references when working with a SQL string in VBA so that the value is passed and not the reference thereby saving the database engine from having to try to evaluate the expression.

How much time consuming if you refer the control in VBA while working with SQL? is there any extra load on database engine while evaluating the expression by passing any reference? When the control "TextBox on a Form" should / should not refer in expression?

 

[boblarson]

How much time consuming if you refer the control in VBA while working with SQL? is there any extra load on database engine while evaluating the expression by passing any reference? When the control "TextBox on a Form" should / should not refer in expression?

It isn't always a "load" issue. What it can be is Access not being able to evaluate the expression sometimes which can cause errors or other problems. So, if you just concatenate in the control references, the VALUE you are looking for is passed which ensures that you get what you want and Access doesn't get confused. And, since it doesn't happen all of the time, it is much easier to just follow that path than to try to figure out when you can let Access evaluate and when you need to concatenate in. So I simplify and just do it all of the time.

 

[Khalid_Afridi]

Agreed and thanks It's always recommended to simplify things before evaluation.