By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

Evaluating dates that contain nulls

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.