Maximum date in the same row 1

[sdempsey]

Hi all,

I have 3 columns with dates in them for the same record and I would like to create a new column in the database that would hold the maximum date (of the 3 other dates).

For example
date 1 23/09/2005
date 2 25/09/2005
date 3 27/10/2005

New column - I would like 27/10/2005 for each row in the table.

I am not sure how this can be completed using a query.

Thanks

Sarah

 

[PHV]

In a standard code module create a function like this:

[green]'A generic function to get the max value of an arbirtrary numbers of same type values:[/green]
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

Now the query:
SELECT field1, ..., fieldN, [date 1], [date 2], [date 3], myMax([date 1],[date 2],[date 3]) As DateMax
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gerard1]

This seems like a very elegant solution to the problem. I have tried to use this on similar data but get an error message when I try to run the query.. (ambiguous name)

I'm sure I've overlooked something, but would appreciate any advice.

Thanks

 

[lespaul]

ambigous name error means that you have two tables in the query and a field in the select is in both tables:

Employees
employeeID
Name
Address
etc

EmployeeSkills
EmployeeID
SkillID

SELECT EmployeeID, EmployeeName, SkillID FROM Employees
INNER JOIN EmployeeSkills on Employees.EmployeeID = EmployeeSkills.EmployeeID

This query will result in an ambigous name error because EmployeeID appears in both tables.

By adding a qualifier, you eliminate this error:
SELECT Employees.EmployeeID, EmployeeName, SkillID FROM Employees
INNER JOIN EmployeeSkills on Employees.EmployeeID = EmployeeSkills.EmployeeID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design