MackMan
Registered User.
- Local time
- Today, 16:28
- Joined
- Nov 25, 2014
- Messages
- 174
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
This returns a null
if I check separately
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
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.
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
Code:
dlookup("lookupValue","qryLookup","TransDate < " & dlast("transdate","qryLookup","TransDate < #" & date & "#"))
This returns a null
if I check separately
Code:
dlast("transdate","qryLookup","TransDate < #" & date() & "#")
Ive tried using an aggregate query with the following for checking the last date before today (which also returns null
Code:
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.