Current Effective date

C

Chris

Is there a function like DMax that will give me the most current effective
date - not a Maximum for the field but the date as of today. I see this
function in another reporting tool, and I want to know if Access has
something similar.
 
T

tina

in Access (queries, forms, reports) use Date().
in Access VBA, use Date.
for current date/time, use Now() or Now.

hth
 
A

Allen Browne

The 3rd argument of DMax() lets you limit it to past dates:
DMax("SomeDateField", "SomeTable", "(SomeDateField < Date()) AND
(SomeOtherField = " & [SomeValue & ")")

If you can live with a read-only result (e.g. if this is for a report),
subquery will be orders of magnitude faster than DMax().

If you can't, and you need to retrieve another field (such as the price that
is effective as of today), this replacement for DLookup() allows you to grab
one field, specify critiera (< Date()), but order by [EffectiveDate] DESC
(i.e. retrive the latest one):
http://allenbrowne.com/ser-42.html
 
Top