simple snytax (lookup most recent record)

[markjkubicki]

this ought to be easy, but I'm missing "something" (probably right in front of my nose)

I'm looking to return the contents of the field Version_ID in the most recent record base on the date in the field Version_Date of that record in the table tblVersionInf; and then assign it to a control property (which does work):



vtxt = DLookup("[version_id]", "tblVersionInfo", DMin("[Version_date]", "tblVersionInfo"))
me.lblVersionInfo.Caption = vtxt


all it's returning is the first record.
(if I change DMin to DMax... same result (so it has to be wrong!!))


with many thanks in advance,
mark

[pbaldy]

You don't have the field to be matched in the criteria of the DLookup().

DLookup Usage Samples

[markjkubicki]

so i changed the code to be:

vtxt = DLookup("version_id", "tblVersionInfo", "version_date = #" & DMin("version_date", "tblVersionInfo") & "#")


and the answer is always FALSE ?

[pbaldy]

What does the DMin() return on its own? And would a simple DMax() on the ID field get you the correct record?

[markjkubicki]

the table, tblVersionInfo, has (3) fields
- Version_ID - long integer, increment
- Version_date - date / time
- Version_notes - long text

what i'm trying to get is the entry of Version_ID for the most recent version_date, so what i presume i need to do is find what is the most recent date in the table (using the DMin() function, and then use that date as the criteria for the lookup. such as:

criteria:
"version_date = #" & DMin("version_date", "tblVersionInfo") & "#"

-no?

[pbaldy]

I understand what you're trying to do. If your version table is like mine, you increment the version with a new date, so for me DMax("VersionNumber", "VersionTable") gets me the most recent version. Your syntax looks fine, which is why I asked what the DMin() returned. Debugging from the inside out.

[markjkubicki]

it's similar to yours, with one exception that i missed including...

the field: [ID], which is an auto-fill and the sequential number of the update's version
of consequence, there may be be a more recent version in progress (to which i am adding notes of what is being included, etc. in a new record, with a new [ID]), but that i am not yet ready to issue. When i do issue the update is when I add the actual date of issue (version_date), and a brief text description (Version_ID) -which would be something like: "version 1.001 - rev: 2016.01.29" b(the 2 dates may actually not align -although they "usually" do...

so the way to know which is the most recent issued version is to look for the most recent date (version_date)

[pbaldy]

Then perhaps:

DMax("VersionNumber", "VersionTable", "DateField Is Not Null")

[markjkubicki]

BINGO! that worked

thnx!

[pbaldy]

Happy to help!