DaniWeb IT Discussion Community - View Single Post

Re: counter using DAO

Dec 1st, 2008

Try This Code. By the r u using MS-Access as database?
If not then modify the Where Clause with language specific syntax for putting the dates in the query.

%%imp%%
Replace the [SALEDATEFIELD] with the date fieldname in your "SalesTable" table.

Help with Code Tags

(Toggle Plain Text)

Set SALE = Dbase.OpenRecordset("select ProdCode, ProdName, sum(Quantity) as Qty, Price from SalesTable Where [SALEDATEFIELD] between #" & dtpicker1.Value & "# AND #" DTPicker2.Value & "# GROUP BY ProdCode, ProdName, Price ORDER BY PRODCODE, PRODNAME, PRICE")

While Not Sale.EOF
    With grd
        .Rows = .Rows + 1
        .TextMatrix(.Rows - 1, 0) = SALE!ProdCode
        .TextMatrix(.Rows - 1, 1) = SALE!ProdName
        .TextMatrix(.Rows - 1, 2) = SALE!Qty
        .TextMatrix(.Rows - 1, 3) = Format((SALE!Price), "#0.00")
        .TextMatrix(.Rows - 1, 4) = Format((SALE!Price * SALE!Qty), "#0.00")
    End With
    total = total + grd.TextMatrix(grd.Rows - 1, 4)
    Sale.MoveNext
Wend

Regards
Shaik Akthar