Too few parameters (Access 2000)

Topic

New Reply

In the function below i get the error ” too few parameters.Can you help me ?

Public Function Dummy()
Dim StrSQL As String
Dim Surcharge As String
Surcharge = ” (IIf([Size]=1,0.138,IIf([Size]=0.4,0.138,IIf([Size]=0.5,0.138,IIf([Size]=4,0.552,IIf([Size]=10,0.552,IIf([Size]=5,0.552,IIf([Size]=18,2.48,IIf(=20,2.66,IIf(=60,6.27,IIf(=180,6.18,IIf(=205,19,IIf(=210,19,0))))))))))))/IIf(<5,1,))"
StrSQL = " UPDATE products SET products.DDU = [exworks] + freight + Surcharge"
CurrentDb.Execute StrSQL

End Function
exwroks is a field in the table and freight is a constant : Public Const freight As Double = 0.3

Reply | Quote

Replies

You cannot use VBA variables or constants in an SQL statement; you have to use concatenation to include them:

StrSQL = “UPDATE products SET DDU = exworks + ” & freight & ” + ” & Surcharge

Reply | Quote

Thank you . I get now the error ” syntax error in update statement”. What may be the reason ?

Public Function Dummy()
Dim StrSQL As String
Dim Surcharge As String
Surcharge = ” (IIf([Size]=1,0.138,IIf([Size]=0.4,0.138,IIf([Size]=0.5,0.138,IIf([Size]=4,0.552,IIf([Size]=10,0.552,IIf([Size]=5,0.552,IIf([Size]=18,2.48,IIf(=20,2.66,IIf(=60,6.27,IIf(=180,6.18,IIf(=205,19,IIf(=210,19,0))))))))))))/IIf(<5,1,))"
StrSQL = "UPDATE products SET DDU = [exworks] + " & freight & " + " & Surcharge

CurrentDb.Execute StrSQL

End Function

Reply | Quote

If your table has fields exworks and DDU, the code should work. Could you do the following?
Above the line CurrentDb.Execute StrSQL, insert a new line

Debug.Print StrSQL

When you run the code, the SQL statement will be output to the Immediate window (in the Visual Basic Editor).
Copy the SQL statement from the Immediate window and paste it into a reply.

Reply | Quote

I could go to the immediate window but i am sending the form and the module. On clicking the control in the form i get the error.I think i have omitted something

Reply | Quote

I don’t get an error at all.

Reply | Quote

It works for me too, so please post the SQL statement from the Immediate window.

Reply | Quote

I have copied the results from the Immediate window as follows

UPDATE products SET DDU = [exworks] + 0,3 + (IIf([Size]=1,0.138,IIf([Size]=0.4,0.138,IIf([Size]=0.5,0.138,IIf([Size]=4,0.552,IIf([Size]=10,0.552,IIf([Size]=5,0.552,IIf([Size]=18,2.48,IIf(=20,2.66,IIf(=60,6.27,IIf(=180,6.18,IIf(=205,19,IIf(=210,19,0))))))))))))/IIf(<5,1,))

Reply | Quote

The cause of the problem is confusion between your system settings (comma as decimal separator) and VBA/SQL (which both expect a point as decimal separator. Concatenating with freight results in 0,3 but SQL expects 0.3. Perhaps you could declare freight as

Const freight As String = “0.3”

Reply | Quote

You are absolutely right ! Thank you !

Reply | Quote