My goal is to get the max date from 5 different fields. These dates are completed dates for five different tasks associated with a project. At the moment I don't care so much about the task, but I want to know the last time any activity took place on a project. The field names are:
I am a new user to Access and I would appreciate any insight or assistance.
Without knowing anymore about your database, it sounds like you need a second and/or a third table. A table that stores Project Activities. It might have the following fields:
ProjectID, ActivityID, Date
Then an Activity table like:
The ProjectActivity table would have multiple records for each project depending on the activities associated with the project. This allows you to add 'unlimited' types of activities instead of just 5. Also, it allows you to answer your question using SQL.
Forgive me if I am misunderstanding your table design. But to answer your question, the only way I can thinkg of to figure out which date is most recent you will have to create a function and call it from a query.
Here is a function:
Public Function MostRecentDate(varDate1 As Variant, _
varDate2 As Variant, _
varDate3 As Variant, _
varDate4 As Variant, _
varDate5 As Variant) As Date
Dim datMostRecent As Date
If Nz(varDate1, 0) > datMostRecent Then datMostRecent = Nz(varDate1, 0)
If Nz(varDate2, 0) > datMostRecent Then datMostRecent = Nz(varDate2, 0)
If Nz(varDate3, 0) > datMostRecent Then datMostRecent = Nz(varDate3, 0)
If Nz(varDate4, 0) > datMostRecent Then datMostRecent = Nz(varDate4, 0)
If Nz(varDate5, 0) > datMostRecent Then datMostRecent = Nz(varDate5, 0)
MostRecentDate = datMostRecent
Create a new module, copy the code to the module, then create a field in a query like this:
SELECT Max(the_date) As [max_date]
SELECT BCDate As [the_date]
) As [some_alias]