Pls Give me quick reply by glancing on this statement what you see wrong in this. I am having problem with a statement for count of number of pieces ordered in less than a date specified. Statement is ::: set rs = cn.execute("select SUM(pieces) from orders where convert(varchar(10),test.[Min_Price]) >'"& MinPrice&"'and date_ordered >'"& Dateordered& "' ") Test is table Min_Price in Ms SQL is data type "Currency" And Pieces field is data type "Float" And date_ordered is Data type "smalldatetime" I requested th above two from the form and trying to input the values for this SQL statemnt but either statement is not right or i am not converting the currency & date fields. What will be the right statement with conversion of currency filed and datetime field for this statement will be for asp in MS SQL server db. set rs = cn.execute("select SUM(pieces) from orders where convert(varchar(10),test.[Min_Price]) > '"& MinPrice&"'and date_ordered >'"& Dateordered& "' ")
The whole statement is messed up. In order to get a sum, you gave to group by a field such as date, category, item, etc. http://msdn2.microsoft.com/en-us/library/ms187810.aspx Also, if the min price is coming from the 'test' table can use a subquery. Try something like this: SELECT SUM(Pieces) AS PiecesSold,date_ordered AS Date FROM orders WHERE Price > (SELECT MinPrice FROM test) GROUP BY date_ordered It should give you the number of pieces sold for each day for order that was above the MinPrice. Hope this helps.
Give this a shot. You do not need to do any conversions. set rs = cn.execute("select SUM(pieces) from orders where test.[Min_Price] > " & MinPrice & " and date_ordered > '" & Dateordered & "'")