Find End Date based on Start Date of next record using DLookup

[mcdownes]

My objective is to populate an end date, based on the start date of the next record for an employee. If no next record, show 12/31 of same year as start date. My question is in two parts. 1) should I use a dlookup, and 2) why is my most basic dlookup giving me an error? Am I using wrong syntax? I thought I would start simple, then play around to see if I can make this work. But even my simple attempt is not working. If there is a better way, I would love to know. Thx for any help!



Table: "ProRationDates", contains [EEID],[FullName],[StartDate]

Query: "ProRationDates Sorted" based on ProRationDates table, contains same fields except 1) I've renamed them [prsEEID],[prsStartDate], and 2) the query is sorted by EEID then StartDate (so I can hopefully do some sort of FindNext function to retrieve the next StartDate for the employee. My logic is that I believe I can identify the current EndDate by using next StartDate -1.

Query2: "Query2" based on ProRationDates table, with an added field called EndDate: DLookUp("[prsStartDate]","ProRationDates Sorted","[prsEEID]=" & [EEID]). But this returns an "ERROR".

[June7]

Domain aggregate functions can be slow performers in queries. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[mcdownes]

This query will be used only to occasionally refresh a table. So, I'm not concerned about slowing performance. Do you have any suggestion for solving my problem?

[June7]

I suggest a nested query as demonstrated in the referenced link.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. If used, enclose name in []. "ProRationDates Sorted" has a space.