Too few parameters (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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 & "))"

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote