Correct formula for a bound text box?

[DPK99]

Hello,

I have leaned how to insert a text box into a form that displays the age by inserting a formula in the control box within properties. I would like to add another box that displays the following result from a table:

The table contains employment information, and the fields are job name, start date, end date, and outcome. I would like for the formula to find the most recent job in the table (based on start date), and if that job has an end date the result is "Not Employed". If the most recently started job does not have an end date, then the result is "Currently Employed".

Can I do that as easily as calculating the age?

 

[GolferGuy]

=IIf(Nz(DLookUp("EndDate", "tblEmployeeJobs", "StartDate=#" & DMax("StartDate", "tblEmployeeJobs", "EmployeeID=" & mEmployeeID) & "# AND EmployeeID=" & mEmployeeID)) = 0, "Currently Employed", "Not Employed")

Basically, this formula gets the StartDate from the table EmployeeJobs based on it being the Max date and for the employee we are looking for (mEmployeeID)
When we have that maximum date, we use that and the employeeID (mEmployeeID) to get the EndDate. By using the Nz() function, if the date is Null or 0, we can conpare to 0 to see if
there is a date. If it is equal to 0, there is no date, therefore "Currently Employed", else "Not Employed"
You will need to change the field names and table name I used to match your own field and table names. If your field or table names have spaces in them, you will need to put brackets "[]" around those names, even inside the double quotes within the DMax and DLookup functions.

 

[DPK99]

Thanks. Here is the formula as when I plug in my field and tables:

=IIf(Nz(DLookUp("[End Date]","tbl[Employment History]","[Start Date]=#" & DMax("[Start Date]","tbl[Employment History]","[Client ID]=" & m[Client ID]) & "# AND [Client ID]=" & m[Client ID]))=0,"Currently Employed","Not Employed")

I receive an error that the expression contains invalid syntax. Can you tell what I am entering incorrectly? It's probably something simple.