counter using DAO
 

gud day, this is my new code of my counter using vb-DAO. if i were going use this code, this will happen (refer to my attachment). the quantity of the first product will added to its proceeding product. That is a very big mistake. My only problem right now is my counter. I want to know which product is most buy by the customer to get this is that I want to compare my product code and then sum up their quantity and display tally of the product being sold within the range of time.

"code"

Private Sub cmdSearch_Click()
Dim total As Currency
Dim x As Integer
Dim i As Integer
Dim Quantity As TableDef
'On Error GoTo abortsub

Set Dbase = OpenDatabase(App.Path & "\Break.mdb")
Set SALE = Dbase.OpenRecordset("Select * from SalesTable")

'Set SALE = Dbase.OpenRecordset("select ProdCode, sum(Quantity) , max(Date) from SalesTable GROUP BY ProdCode, Date;")

SALE.MoveFirst
Do Until SALE.EOF

If DTPicker1.Value <= SALE!Pitsa And _
DTPicker2.Value >= SALE!Pitsa Then

If SALE!ProdCode = SALE!ProdCode Then
For i = 0 To SALE.EOF
x = x + SALE!Quantity

'For Each Quantity In SALE!Quantity
'x = x + SALE!Quantity

With grd
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 0) = SALE!ProdCode
.TextMatrix(.Rows - 1, 1) = SALE!ProdName
.TextMatrix(.Rows - 1, 2) = x 'SALE!Quantity + SALE!Quantity
.TextMatrix(.Rows - 1, 3) = Format((SALE!Price), "##.00")
.TextMatrix(.Rows - 1, 4) = Format((SALE!Price * .TextMatrix(.Rows - 1, 2)), "##.00")
End With
total = total + grd.TextMatrix(grd.Rows - 1, 4)
Next i
End If
End If

SALE.MoveNext
Loop
grd.Rows = grd.Rows + 1
grd.TextMatrix(grd.Rows - 1, 3) = "Total"
grd.TextMatrix(grd.Rows - 1, 4) = Format(total, "#,##0.00")


'abortsub:
'Exit Sub
End Sub

Re: counter using DAO
 

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.

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