JL, hate to bother you again, but I noticed a slight problem with your code
(actually it was how I described my problem). I forgot to mention that I
will have 100's of ID's in my database in which I will need to perform a
previous record look-up. You code works well, however, when you have
multiple IDs, the results look something like this using your sql statement:
Id Date Return PrevDate PrevReturn
1 03/01/2004 1.0% Blank Blank
1 04/01/2004 2.5% 03/01/2004 1.0%
1 05/01/2004 -5.0% 04/01/2004 2.5%
2 05/01/2004 4.0% 04/01/2004 2.5%
Ideally, for the first record, the PrevRet should equal current month
return. How can this be coded?
Araca said:
JL, your suggestion works well, thank-you.
:
Hi Araca,
Try this.
Table1: ID, DateF, Return
SELECT Table1.ID, Table1.DateF, Table1.Return,
DMax("[DateF]","Table1","[DateF]<#" & [DateF] & "#") AS PrevDate,
iif(isnull(PrevDate), "", DLookUp("[Return]","Table1","[DateF] = #" &
PrevDate & "#") ) AS PrevReturn
FROM Table1;
It will list all the field that you need in the query.
Hope this will help.
:
Can someone please help me correctly use DLookUp in my Query. I basically
have a table with the following fields:
ID, Date, Return
For each ID, I want to select the previous date and return in order to
compute an annualized yield for my investments. How best can I do this
within my query using DLookUp or is there a better way to extract this info??
