Lookup Value from next record using DLOOKUP

[eebabe]

Hi:

This is my formula:

=DLookUp("[CONTACT/REF DATE]","tbl_Referred","[NAMEDATE] = '" & [Reports]![rpt_Ref_Clients_All]![NAMEDATE] & "'")

This works to look up the same value of the same record but I would like [CONTACT/REF DATE] value from the next record and so I tried:

=DLookUp("[CONTACT/REF DATE]","tbl_Referred","[NAMEDATE] = '" & [Reports]![rpt_Ref_Clients_All]![NAMEDATE] & "'+1")

But that didn't work. If the formula is:

DLookUp("[Contact/Ref Date]","tbl_Referred","[ID]=Reports![rpt_Ref_Clients_All]![ID]+2") --> This will result in the date of the next record.

Does it work only with numbers? Is there any other way to do this?

Thanks.

 

[Golom]

How did it fail exactly?
- Error?
- No value ?
- Wrong value?

Adding a numeric value does work only with numbers. Dates are, however, numbers. The potential problem is that the next date is not necessarily the following day which is what +1 implies for dates.

You might try the DMin function

=DMin ("[CONTACT/REF DATE]","tbl_Referred", _
       "[NAMEDATE] > '" & [Reports]![rpt_Ref_Clients_All]![NAMEDATE] & "'" )

[NAMEDATE} would of course need to have to have unique values for this to be reliable and you will need to deal with the "last record" issue where there is no next record.