to find a closest year and month

[Alex]

I have a table with three columns:
Year Month Rate

1999 1 1.3
1999 2 1.5
2001 1 1.4
2001 3 1.35
2002 5 1.3
..
..
How could I choose the closest to date rate by using a
query or DLookup function.

Thanks

 

[[MVP] S.Clark]

You could do it with the following steps:

1. Get Max of Year. x = Dmax("Year", "Tablename")
2. Next, Find Max Month, using the Max year. y = Dmax("Month", "Tablename",
"Year = " & x)
3. Finally, rate = Dlookup("Rate", "TableName", "Year = " & x & " AND Month
= " & y)

 

[Alex]

Thanks a lot, Steve.

-----Original Message-----
You could do it with the following steps:

1. Get Max of Year. x = Dmax("Year", "Tablename")
2. Next, Find Max Month, using the Max year. y = Dmax ("Month", "Tablename",
"Year = " & x)
3. Finally, rate = Dlookup("Rate", "TableName", "Year = " & x & " AND Month
= " & y)

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting




.