Find Last date & value, before today with empty dates (1 Viewer)

[MackMan]

Hi all.

I'm trying to lookup a value from another query with dates running in ASC order from qryLookup

TransDate Lookup Value
----------- ---------------
20/07/2018 … zz1
20/07/2018 … xz1
21/07/2018 ... xy1
21/07/2018 ... xy2
23/07/2018 ... xy3
23/07/2018 ... xy4
23/07/2018 ... xy5 < -----------
25/07/2018 ... xy6
25/07/2018 … xy7

What I want to do is lookup the last record before today (being 25/07/2018)
and get the value xy5

I've tried

dlookup("lookupValue","qryLookup","TransDate < " & dlast("transdate","qryLookup","TransDate < #" & date & "#"))

This returns a null

if I check separately

dlast("transdate","qryLookup","TransDate < #" & date() & "#")

then I get a return of the 20th with 2 dates inbetween ( which is not the last date before today)

Ive tried using an aggregate query with the following for checking the last date before today (which also returns null

SELECT Last(qryLookup.TransDate) AS LastOfTransDate
FROM qryLookup
HAVING (((Last(qryLookup
.TransDate))<Date()));

Dmax also returns the same

Am I doing something so obviously wrong? or is there a more difficult solution I've not yet come across (so I don't know it)?

As always, your help is much appreciated.

 

[Minty]

You final approach is correct, but your use of Last is tripping you up. It's a horrible function and doesn't do what you think it might.

Try that using Max()

There is a discussion here about Last and it failings https://access-programmers.co.uk/forums/showpost.php?p=422383&postcount=7

 

[CJ_London]

A variation of your last approach is

SELECT TOP 1 TransDate
FROM qryLookup
WHERE TransDate<Date()
ORDER BY TransDate Desc

 

[MackMan]

Thanks Minty for your link. Now I understand my error and it's easy to see why.
And thanks CJ London once again... does exactly as I need it to!
You guys are awesome! Total respect in regards to your knowledge on all this stuff.

It's still a minefield for me, but I'm getting there.

 

[arnelgp]

You set the Sort Order of qryLookup to TransDate Desc, [Lookup Value] Desc.

or create another query with the above sort order and use it for your dlookup.