Seniority List Query with Two Date Fields

[Nuke1096]

I made a basic Seniority List for my work place using a simple query that pulls info from a main Employee Table. Easy enough using just a simple query with a sort. Problem is, about a dozen or so of the Employee have left and since been rehired. Their Seniority is based on their Rehire Date and not their Original Hire Date. In the main Employee Table I am pulling this info from, there are two Date Fields...

[Hire Date]

[Rehire Date]

Is there a way to build a query where it can check both fields, use the more recent date, then copy the "winner" in a 3rd field? Am I approaching it all wrong? Is there an easier simpler way to do that?

[ranman256]

Dont use a 3rd date field, create a UseDate fld using an IIF : IIF(Isnull([Rehire Date]) or [Rehire Date]>[Hire Date],[Hire Date],[Rehire Date]) as UseDate

[Nuke1096]

Sorry, I'm a little confused as to how to use that code? Should that go in a new query field?

[rpeare]

Create a query based on your table. Type in this formula in a new field:

SeniorityDate: Iif(isnull([Rehire Date]), [Hire Date], [Rehire Date])

This assumes the rehire date is always going to be larger (more recent) than the hire date

[Nuke1096]

Yup, that did exactly what I wanted it to do. Saves me a bunch of time, thank you!

[hapm]

Only a question of interest: how do you handle someone, who left twice and was therefor rehired 3 times?