Re: How to Find the Date of Last Change in Inventory

Thanks. I have done the following, as you suggested:

SLOBInventorytbl:
The table which holds all the data. The keys are Style, Color,
SLOBDate.

SlobInv1StyColqry:
I created this query which groups on Style and Color. It is based on
SLOBInventorytbl.

SLOBInv2StyColDatUntqry:
I created this query, based on SLOBInventorytbl, with these fields in
this order: Style, Color, SLOBDate, OnHand. (OnHand (Units) is the
value, a quantity of units in the warehouse.) There are no "Grouped
By"s on any of these fields because the table's keys make it
superfluous. The Style and Color fields are marked Ascending. The
SLOBDate field is marked Descending, as you instructed.

I'm not sure what to do with your code. But I did rename the fields.
Here it is now:

Set qdf = Currentdb.QueryDefs("SlobInv1StyColqry")
Set rstItems = qdf.OpenRecordset(dbOpenSnapsh­ot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Do While Not rstItems.EOF
Set qdf = Currentdb.QueryDefs("SLOBInv2StyColDatUntqry")
qdf.Parameters(0) = rstItems![Style]
qdf.Parameters(1) = rstItems![Color]
Set rstInventory = qdf.OpenRecordset(dbOpenSnapsh­ot, dbReadOnly)
rst.MoveLast
rst.MoveFirst
dtmLastDate = rstInventory![SLOBDate]
intCount = rstInventory![OnHand]
rstInventory.MoveNext
If rstInventory.EOF Then
'No movement here
End If
Do While Not rstInventory.EOF
If rstInventory!OnHand = intCount Then
dtmFirstDate = rstInventory!Units
Else
'Now you have the count and dates to calculate
Exit Do
End If
rstInventory.MoveNext
Loop
rstInventory.Close
rstItems.MoveNext
Loop
__________________________________

Thanks,

Matt

.