Current Effective date

[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.

 

[tina]

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

hth

 

[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