Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too few parameters (Access 2000)

    With the help of Hans, a member of the present Forum, i have built a sucessful function for deleting dates before a given value.
    First i want to thank for the precious help i have received a lot from the Forum.My question now is how can i insert a constant in the function,
    so as to make it more flexible.Mi idea is to have a constant :
    And then replace it instead of the dates for example:
    " WHERE (((orders.orderdate) <CnstBefore))" ' month,day,year

    However, i receive the message "too few parameters".Can somebody explain why is it so?I am giving first the
    successful function called "DeleteAllBefore" and then my effort to build the new function called "DeleteAllbefore1" which is not
    working, and giving message "Too few parameters"




    Public Const CnstBefore As Date = #6/1/2003#
    Public Function DeleteAllBefore()
    ' month, day,year

    Dim SqlRemoveFromOrders
    Dim SqlRemoveFromOrderDetails
    SqlRemoveFromOrders = "DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
    " FROM orders WHERE (((orders.orderdate)<#5/1/2003#));" ', month,day,year

    SqlRemoveFromOrderDetails = " DELETE DISTINCTROW [order details].*, orders.orderdate " & _
    "FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID " & _
    " WHERE (((orders.orderdate) <#5/1/2003#))" ' month,day,year
    CurrentDb.Execute SqlRemoveFromOrderDetails
    CurrentDb.Execute SqlRemoveFromOrders

    End Function

    Public Function DeleteAllBefore1()
    ' month, day,year

    Dim SqlRemoveFromOrders
    Dim SqlRemoveFromOrderDetails
    SqlRemoveFromOrders = "DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
    " FROM orders WHERE (((orders.orderdate)<CnstBefore));" ', month,day,year

    SqlRemoveFromOrderDetails = " DELETE DISTINCTROW [order details].*, orders.orderdate " & _
    "FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID " & _
    " WHERE (((orders.orderdate) <CnstBefore))" ' month,day,year
    CurrentDb.Execute SqlRemoveFromOrderDetails
    CurrentDb.Execute SqlRemoveFromOrders

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Too few parameters (Access 2000)

    What is "month, day, year" doing in there? Is this a comment tacked onto the very end of the SQL string? As has been mentioned before, queries can't use variables or constants, so you can't write the SQL to include the reference to the constant. You have to concatenate the constant value to the SQL string insteadc.

    " WHERE (((orders.orderdate) <CnstBefore))"

    should be

    " WHERE (((orders.orderdate) < " & CnstBefore & "))"
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    Thank you for your reply.I have followed your advice,but i am afraid i didnt follow it properly,since i receive this time Syntax error.
    Obviously my line
    < " & CnstBefore & "))"

    is not right.May you help me write it correctly?

    best regards
    ===============================

    Public Const CnstBefore As Date = #6/1/2003#
    Public Function DeleteAllBefore1()

    Dim SqlRemoveFromOrders
    SqlRemoveFromOrders = "DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
    " FROM orders WHERE WHERE (((orders.orderdate) < " & CnstBefore & "))"

    CurrentDb.Execute SqlRemoveFromOrders
    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)

    Since you are concatenating the date in a string, it must be enclosed in # signs, and if your local date settings are not the same as those in the US (mm/dd/yyyy), you must ensure that the date is formatted conform to US date style. Also, you have duplicated WHERE now, but that's just a typo, I presume. Try the following:

    SqlRemoveFromOrders = "DELETE DISTINCTROW orderdate" & _
    " FROM orders WHERE orderdate < #" & Format(CnstBefore, "mm/dd/yyyy") & "#"

    (Note that I have also removed some superfluous elements that Access adds to a query when you use the design grid)

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too few parameters (Access 2000)

    Thank you very much for your reply.Could you please check up what i have done, since i am afraid i do make some errors in my
    code and i receive the message " syntax error in query expression orderdate< #1/1/2002# "

    Public Const CnstBefore As Date = #1/1/2002#
    Public Function Test()
    Dim SqlRemoveFromOrders
    SqlRemoveFromOrders = "DELETE DISTINCTROW orderdate FROM orders WHERE orderdate < #" & Format(CnstBefore, "mm/dd/yyyy") & "#"
    CurrentDb.Execute SqlRemoveFromOrders
    End Function

    Best regards

  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 know if this will work, but try changing:
    SqlRemoveFromOrders = "DELETE DISTINCTROW orderdate FROM orders WHERE orderdate < #" & Format(CnstBefore, "mm/dd/yyyy") & "#"
    to:
    SqlRemoveFromOrders = "DELETE DISTINCTROW orderdate FROM orders WHERE orderdate < " & CnstBefore

    It's just a hunch.

  7. #7
    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 have now tested the following and it works:

    Public CnstBefore As Date

    Private Sub CommandDelete_Click()
    CnstBefore = #1/1/2002#
    Dim SqlRemoveFromOrders
    SqlRemoveFromOrders = "DELETE DISTINCTROW date FROM orders WHERE date < #" & Format(CnstBefore, "mm/dd/yy") & "#"
    CurrentDb.Execute SqlRemoveFromOrders
    End Sub

Posting Permissions

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