Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too few parameters (Access 2000)

    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([size]=20,2.66,IIf([size]=60,6.27,IIf([size]=180,6.18,IIf([size]=205,19,IIf([size]=210,19,0))))))))))))/IIf([size]<5,1,[size]))"
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too few parameters (Access 2000)

    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

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    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([size]=20,2.66,IIf([size]=60,6.27,IIf([size]=180,6.18,IIf([size]=205,19,IIf([size]=210,19,0))))))))))))/IIf([size]<5,1,[size]))"
    StrSQL = "UPDATE products SET DDU = [exworks] + " & freight & " + " & Surcharge

    CurrentDb.Execute StrSQL

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too few parameters (Access 2000)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Too few parameters (Access 2000)

    I don't get an error at all.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too few parameters (Access 2000)

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

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    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([size]=20,2.66,IIf([size]=60,6.27,IIf([size]=180,6.18,IIf([size]=205,19,IIf([size]=210,19,0))))))))))))/IIf([size]<5,1,[size]))

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too few parameters (Access 2000)

    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"

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    You are absolutely right ! Thank you !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •