Looking at several date fields in a table

  • Thread starter Thread starter Lurch
  • Start date Start date
L

Lurch

Hi there! I sure hope that you can help me as I've wasted a TON of time
trying to figure this out.

Let's say I have four date fields in a table. One field may have a date or
two fields may have dates, up to all four. Let's call them Date1, Date2,
Date3, Date4. These are dates that my project was approved but based upon
it's cost the approval goes all the way 'up the line' to Date4 if the "Big
Guy's" approval was needed.

What I need to know is the "Absolute" date the approval was given.

How would I code a function (preferable) or create a query that would look
at Date4 first. If there is a date in Date4 then put that value in a field
called [dtmApproved_Final_Date]. If there is nothing in Date4 then look in
Date3. If there is a date in Date3 then put that value in a field called
[dtmApproved_Final_Date]. If nothing in Date3 then look in Date2 and so on.

Lastly (thanks for sticking with me here), how do I get this to put the
Final Approved Date value into a table with the [dtmApproved_Final_Date]
field? I would prefer this value to be placed into the same table as the 4
date fields are in. Is that possible?

Any help would be GREATLY appreciated.

Thanks Guys!

Tony
 
Hi there! I sure hope that you can help me as I've wasted a TON of time
trying to figure this out.

Let's say I have four date fields in a table. One field may have a date or
two fields may have dates, up to all four. Let's call them Date1, Date2,
Date3, Date4. These are dates that my project was approved but based upon
it's cost the approval goes all the way 'up the line' to Date4 if the "Big
Guy's" approval was needed.
Click to expand...

Some day your company may be bought out and the Big Guy's boss may
need to approve too - and you'll then need a Date5 (or the Big Guy may
insert another level of middle managers and you'll still need a
Date5). Are you ABSOLUTELY sure that this shouldn't be a one to many
relationship to an Approvals table? If you had the data normalized
into two tables, as simple Max() totals query would find the latest
approval date.
What I need to know is the "Absolute" date the approval was given.

How would I code a function (preferable) or create a query that would look
at Date4 first. If there is a date in Date4 then put that value in a field
called [dtmApproved_Final_Date]. If there is nothing in Date4 then look in
Date3. If there is a date in Date3 then put that value in a field called
[dtmApproved_Final_Date]. If nothing in Date3 then look in Date2 and so on.
Click to expand...

dtmApprovedFinalDate: NZ(Date4,NZ(Date3,NZ(Date2,Date1)))
Lastly (thanks for sticking with me here), how do I get this to put the
Final Approved Date value into a table with the [dtmApproved_Final_Date]
field? I would prefer this value to be placed into the same table as the 4
date fields are in. Is that possible?
Click to expand...

Run an Update query updating dtmApproved_Final_Date to the above
expression.

OR... better... normalize your table structure, and just find the max
approval date on the fly.
 
Back
Top