DLOOKUP Cost from date less than or equal to date 1

[crimedog]

Cost can fluctuate through the year on a month to month basis


I want to create a report January - Feb... that has the cost for each month
Table CostChange has [Date] [Item] [Cost] with a record ONLY when there is a change

so: Item X has a cost in Jan and a new cost in Mar

I want report:

Jan Dlookup([Cost],[CostChange],[Date]<=1/31/2104 (should return Jan Cost)
Feb Dlookup([Cost],[CostChange],[Date]<=2/28/2104 (should return Jan Cost)
Mar Dlookup([Cost],[CostChange],[Date]<=3/31/2104 (should return Mar Cost)

What am I missing?

[roberth]

To my knowledge Dlookup will return the first record it finds (also I do not believe there is a way to sort prior to the search). Which means for your table it will find the first record that has a date <= 1/31/2014. The first record that happened to match the Dlookup would be returned. ie
2/10/2014
1/20/2014
2/20/2014
3/20/2014
Mar Dlookup([Cost],[CostChange],[Date]<=3/31/2104
Would return the cost from 2/10/2014.
It would be better to have at a minimum an AND clause if there is only at most one report per month. (otherwise it will essentially randomly return one of the reports)

This is one way you could make that work at least for access 2007
Jan: Dlookup([CostChange]![Cost],"CostChange","[Date]<=#1/31/2014#" And "[Date]>=#1/1/2014#")
*Costchange = Table
*Cost = Field
I'm guessing this is backwards, but I'm trying to match what you put above. Let me know if this is not clear.
but again note that this will only work if there is at most one report per month showing cost change.

One of the more experienced members probably has a better solution for you. Dlookup probably is not the right tool for what you want.

[June7]

Cost of what?

Is there only 1 cost record for each month? Note the quote marks enclosing the arguments.

Jan Dlookup("[Cost]","[CostChange]","Format([Date],'yyyymm'='201401'"

Domain aggregates can be slow performers. Could construct queries that calculate the yyyymm value for both datasets and then join those queries on the common fields.

[crimedog]

that looks good - but there isn;t a change every month. and all the items don't change at the same time. that is why I am looking for the closest to date.
so if the cost is set in Jan then updates in june, jan-may should be the jan cost. jun-dec should be the jun cost

[June7]

You need the cost for each item for each month? Won't be easy. Criteria that is dependent on data in other records of same table usually very tricky. See if this works with your data. I tested with a very small dataset and calculated only 3 months.

ID	ItemID	Date	Cost
1	1	1/1/2014	10
2	2	1/1/2014	15
3	1	4/1/2014	20
4	3	5/1/2014	11
5	2	11/1/2014	13

Query1
SELECT "201401" AS YrMo, ItemID, CDate(Nz(DMax("[Date]","CostChange","ItemID=" & ItemID & " AND [Date]<=#1/1/2014#"),#1/1/1900#)) AS Nearest FROM CostChange
UNION SELECT "201402", ItemID, CDate(Nz(DMax("[Date]","CostChange","ItemID=" & ItemID & " AND [Date]<=#2/1/2014#"),#1/1/1900#)) FROM CostChange
UNION SELECT "201411", ItemID, CDate(Nz(DMax("[Date]","CostChange","ItemID=" & ItemID & " AND [Date]<=#11/1/2014#"),#1/1/1900#)) FROM CostChange;

Query 2
SELECT Query1.ItemID, Query1.YrMo, Max(Query1.Nearest) AS MaxOfNearest
FROM Query1
GROUP BY Query1.ItemID, Query1.YrMo;

Query3
SELECT CostChange.ItemID, Query2.YrMo, CostChange.Cost, Query2.MaxOfNearest
FROM Query2 INNER JOIN CostChange ON (Query2.MaxOfNearest = CostChange.Date) AND (Query2.ItemID = CostChange.ItemID)
ORDER BY CostChange.ItemID, Query2.YrMo;


BTW, Date is a reserved word. Should not use reserved words as field names.

[crimedog]

I was thinking what if I change the date to month : Round(Month([Changedate],0) then I compare against 1,2,3...

[June7]

I don't really understand that. Why would you need Round function? Are there multiple years in your db? Need year part for the aggregate query.

Did you try what I suggested?