Comparing Dates across Multiple Fields

[blatty]

If I have four date fields and I wanted to compare all four and then display the latest date in another field is this possible?

Example:

Fields are:
Step1Date
Step2Date
Step3Date
Step4Date
OutlookDate

Compare All StepDates (1-4) and find Latest StepDate

OutlookDate = Latest StepDate (based on comparing all four)

 

[boblarson]

First of all, you should not have repeating fields. That shows you do not have normalized data and because of that you are running into a brick wall when trying to pull meaningful data from your database. If you had this designed properly it would be nothing to pull this data you want. But, due to design flaws you will have to struggle with getting what would normally be very easy.

So, that being said, you can display all of the dates on a form and then use some code to determine the latest. Or, you can create a function to be able to pull the latest into a query. What type of output do you need?

 

[blatty]

Thanks Bob,

I need my access form to be able to perform the following:

All projects follow an 8 step process to completion. Each step has multiple tasks that need to be accomplished before that step is complete.

When a project manager estimates completion dates for a particular project he populates the individual task dates (each task has a separate field on the access form.

The project manager on a monthly basis will update any dates as necessary.

The department manager does a monthly review and does not want to have to look at all of the individual tasks (steps) but wants to look at one field that indicates the current outlook (separate field on access form) for that particular step of the project.

I need to compare all estimated task dates in a specific step, determine which date is the latest and then populate the Current Outlook field with that date information.

All fields are in the same table.

I don't know any other way to contruct my database to accomplish the desire of my boss.

Blatty