Total Orders from Date Range 1

[GulfImages]

Hello,

I have some code that I'm trying to make work. What I want it to do is Open a query that selects orders based on a query that gets dates from a form (Finally got rid of that pesky 3061 error!), runs through the recordset, grabs each order # then runs through the Order Items table and adds up all the items for all the order ID's in the order table. Here is the code:

Public Function GrossIncome()

Dim Items, InvNo

Dim OrdIn As Database, Invoice As Recordset
Set OrdIn = DBEngine.Workspaces(0).Databases(0)


Set Invoice = OrdIn.OpenRecordset("qryInvByDate", dbOpenDynaset)

'I have verifyed that the query is getting the records I want

Invoice.MoveFirst

Do Until Invoice.EOF

InvNo = Invoice!OrderID 'Get an order #

If Invoice.NoMatch Then
GrossIncome = Format(0, "currency")
Invoice.Close
Set OrdIn = Nothing
Set Invoice = Nothing
Exit Function
End If

' Run through the Order Items table and add up items

Dim InvI As Database, InvItems As Recordset
Set InvI = DBEngine.Workspaces(0).Databases(0)
Set InvItems = InvI.OpenRecordset("tblInvoicesItems", DB_OPEN_TABLE)

InvItems.MoveFirst

Items = 0

Do Until InvItems.EOF

If InvItems!OrderID = InvNo Then Items = Items + (InvItems!UnitPrice * InvItems!Quantity)

InvItems.MoveNext

Loop

'Move to the next Order ID in the Orders table
Invoice.MoveNext

Loop

Invoice.Close
InvItems.Close

Set OrdIn = Nothing
Set Invoice = Nothing

Set InvI = Nothing
Set InvItems = Nothing


'total the items

If Items = 0 Then
GrossIncome = Format(0, "currency")
Exit Function
End If

GrossIncome = Format(Items, "currency")

End Function

The problem is I'm only getting a total for the last Order # listed in the qryInvByDate.

Any suggestions?

Thanks,
Bobby C.

 

[PHV]

And what about an aggregate query, something like this ?
SELECT OrderID, Sum(UnitPrice * Quantity) As Total
FROM tblInvoicesItems
GROUP BY OrderID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GulfImages]

I think that would work, but my problem is the second loop is not going to each OrderID in the "qryInvByDate" and totalling them, it's only totalling the last OrderId in "qryInvByDate". I want it to loop through "qryInvByDate", take each orderID, then loop through tblInvItems and total the items for all the OrderId's returned from "qryInvByDate"

Am I making sense?

 

[GulfImages]

Well, you got me to thinking and your right, a simple aggregate query as you listed except using the "qryInvByDate" joined with "tblInvoicesItems" as the source instead of just tblInvoicesItems, grouped by OrderID, gives me the results that just need to be totalled.

Thanks a bunch!