Complicated textbox expression challenge

[Wombat]

I have a table called Employee, another called EmployeeLOA (leave of absence) and a third called EmployeeTermination. I want to create a textbox on a form bound to Employee that displays LOA, Terminated or Active.



Active is assumed if the employee is not Terminated or on LOA.

If there are any Termination dates, the employee can be marked as Terminated

BUT for the leave of absence, Every time the employee takes an LOA, an LOADate and ReturnDate are recorded. I need to find the most recent LOADate for this employee and check to see if the current date is between that and the Return date.


So far, I can't even get it to tell me LOA or not.

[June7]

You need to develop a query that joins the tables and has criteria and/or expressions in calculated fields to manipulate the data. Really don't know your data well enough to be specific. Then the form RecordSource might include the query or the textbox could do a DLookup on the query. Again, don't know enough to be specific.

[Wombat]

I think you may have just helped. I tend to get too focused on trying to write one statement that gets me down to one answer and then I don't even know how to get that answer into a textbox. I think writing a query with some calculated fields and then Dlookup off of that could be the ticket.

Thanks