Evaluating dates that contain nulls

[Steve]

I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)

Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions

[Mike MacSween]

"Steve" <sv*******@msn.com> wrote in message
news:2d**************************@posting.google.c om...

I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)

Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions

Try Nz() function.

1. Or better still, change your table structure. I'm not sure what the exact
purpose of this database is, but does each employee only have zero or one
accidents? Probably not.

So I think you need a table of accidents, joined many to one to employee, on
the Emp field.

2. If Eligibility is just the maximum of Hire Date, Accident Date, and Award
Date, then that's 'derived data'. Meaning you can work it out from the data
you've got. So you don't need to do an update query, because you shouldn't
be storing eligiblity date atall.

Mike

[Steve]

Thanks for the feedback Mike. I need elgibility date because there
are calculations that need to know how many hours has someone worked
after their elibility date which changes if they have been given an
award or were in a accident. I am just struggling to come up with it
because of the nulls. I wish it wer in excel then it a simple
solution!

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@pubnews.gradwell.n et>...

"Steve" <sv*******@msn.com> wrote in message
news:2d**************************@posting.google.c om...

I am trying to evaluate the latest occurence of a date which will go
in the field titled eligibility date. Table is set up as follows and
am using update query to update the eligibility date to the latest of
hire,award, or accident)

Emp Hire Date Accident Date Award Date eligibility
Date
Hire Date is never a null, however if there are no accidents or awards
those respective fields will be null. I tried using if and max
statements but to no avail. Any suggestions

Try Nz() function.

1. Or better still, change your table structure. I'm not sure what the exact
purpose of this database is, but does each employee only have zero or one
accidents? Probably not.

So I think you need a table of accidents, joined many to one to employee, on
the Emp field.

2. If Eligibility is just the maximum of Hire Date, Accident Date, and Award
Date, then that's 'derived data'. Meaning you can work it out from the data
you've got. So you don't need to do an update query, because you shouldn't
be storing eligiblity date atall.

Mike