Dlookup based on compared record date

[Dkub]

I've been reading and working with The Doc Man's excellent thread about dependent lookups (the system won't let me post the link for some reason, so here's the end of address that should get you there: showthread.php?t=310382).

I know this is really close to what I need, but I haven't been able to make it work.

My table is tblEmployeePromoTrans; there is a record for each promotion or transfer, so any given employee could have multiple records based on how many promotions/transfers they've had over the past years.

In my query, if my focus record is [Type]="Trans", I need my [LastPromoDate] field to 1) check if there have been any promotions, and then 2) provide the very latest promotion date prior to the transfer date of the focus record.

I think I have to be close, but I still get #Error in my field when I run the query. Here is the field's expresssion:

LastPromoDate: IIf([Type]="Trans",DLookUp("PromoEffDate","tblEmployeePromoTrans","EmplID = " & [qryEmployeeRecords1bUNION]![EmplID] & " AND Type = 'Promo'" & " AND [tblEmployeePromoTrans]![TransEffDate] = #" & CStr(DMax("TransEffDate","tblEmployeePromoTrans","[tblEmployeePromoTrans]![TransEffDate] < #" & CStr([TransEffDate]) & "# )") & "#")),Null)


Any and all help would be very much appreciated!

 

[Dkub]

Thanks to those who viewed this thread. I found a workaround using a subquery.