Comparing multiple Dates in a row

[Guest]

I have a query that has 7 date fields, I've used an inner join query to
create and due to the nature of my data, I need to have the 7 date fields as
they are. I now need an additional field in the query that shows the
earliest date. I know I can do a nested Iif statement, but I think it will
be too long to fit in an expression. Can anyone help me with some code that
would achieve this? Thank you.

 

[Tom Ellison]

Dear Alex:

You have three choices here. You can correct the database so it is properly
normalized, so you only have one of these 7 date columns in a row. Now, I
don't know what these 7 represent. For example, if there is any way you
would ever need to add more such dates, having them in a separate table with
one date per row would make this much easier. Both the database and the
applications to work with it could then be crafted to work correctly. You
could use the MIN() function to find the earliest date.

A second way to approach this is to make a UNION query that places the 7
dates so they appear as though the database were correctly constructed, and
then you could again use the MIN() function.

The final approach would be to write a custom Function to compare the dates
and return the smallest value. You could then reference this in any query.
This would be a VBA function for a Jet database or a UDF for MSDE.

The first approach above is a bit more work, but will be most easily
maintained in the future. If you doubt this approach, then I suggest you
need to study the rules of normalization and begin to learn just how
valuable they are.

The second approach presents a compromise between actually fixing the
database and yet losing some of its functionality. If you needed an 8th
date, you would have to modify the table and the UNION query.

The third approach is a heavy handed, but perhaps quickest approach. It
does require VBA coding skills, although there are probably samples of this
code available.

Tom Ellison

 

[Brendan Reynolds]

Public Function MaxDate(ParamArray Values()) As Date

Dim varWork As Variant
Dim dtmResult As Date

dtmResult = #1/1/100#
For Each varWork In Values
If varWork > dtmResult Then
dtmResult = varWork
End If
Next varWork

MaxDate = dtmResult

End Function

Example of use in query, using Orders table from Northwind ...

SELECT Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
MaxDate([OrderDate],[RequiredDate],[ShippedDate]) AS Expr1
FROM Orders

 

[Guest]

Thank you both for your help. I'm weighing my options, but have one
question. If use the code from Brendan I get #error in the rows where all of
my dates are null. How can I change the code to leave the MaxDate blank if
all of the date fields in that row are blank? Thanks.

Public Function MaxDate(ParamArray Values()) As Date

Dim varWork As Variant
Dim dtmResult As Date

dtmResult = #1/1/100#
For Each varWork In Values
If varWork > dtmResult Then
dtmResult = varWork
End If
Next varWork

MaxDate = dtmResult

End Function

Example of use in query, using Orders table from Northwind ...

SELECT Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
MaxDate([OrderDate],[RequiredDate],[ShippedDate]) AS Expr1
FROM Orders

--
Brendan Reynolds

I have a query that has 7 date fields, I've used an inner join query to
create and due to the nature of my data, I need to have the 7 date fields
as
they are. I now need an additional field in the query that shows the
earliest date. I know I can do a nested Iif statement, but I think it
will
be too long to fit in an expression. Can anyone help me with some code
that
would achieve this? Thank you.

 

[John Spencer]

I would try changing the first line to

Public Function MaxDate(ParamArrayValues()) as Variant
....

And changing MaxDate = dtmResult to

If DtmResult = #/1/1/100# then
MaxDate = Null
Else
MaxDate = dtmResult
End If

End Function

Thank you both for your help. I'm weighing my options, but have one
question. If use the code from Brendan I get #error in the rows where all
of
my dates are null. How can I change the code to leave the MaxDate blank
if
all of the date fields in that row are blank? Thanks.

Public Function MaxDate(ParamArray Values()) As Date

Dim varWork As Variant
Dim dtmResult As Date

dtmResult = #1/1/100#
For Each varWork In Values
If varWork > dtmResult Then
dtmResult = varWork
End If
Next varWork

MaxDate = dtmResult

End Function

Example of use in query, using Orders table from Northwind ...

SELECT Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
MaxDate([OrderDate],[RequiredDate],[ShippedDate]) AS Expr1
FROM Orders

--
Brendan Reynolds

I have a query that has 7 date fields, I've used an inner join query to
create and due to the nature of my data, I need to have the 7 date
fields
as
they are. I now need an additional field in the query that shows the
earliest date. I know I can do a nested Iif statement, but I think it
will
be too long to fit in an expression. Can anyone help me with some code
that
would achieve this? Thank you.

 

[Brendan Reynolds]

Or, perhaps, add a criteria to the query to exclude those records ....

WHERE ([Date1] IS NOT NULL) OR ([Date2] IS NOT NULL) OR ... etc.

--
Brendan Reynolds

I would try changing the first line to

Public Function MaxDate(ParamArrayValues()) as Variant
...

And changing MaxDate = dtmResult to

If DtmResult = #/1/1/100# then
MaxDate = Null
Else
MaxDate = dtmResult
End If

End Function

Thank you both for your help. I'm weighing my options, but have one
question. If use the code from Brendan I get #error in the rows where
all of
my dates are null. How can I change the code to leave the MaxDate blank
if
all of the date fields in that row are blank? Thanks.

Public Function MaxDate(ParamArray Values()) As Date

Dim varWork As Variant
Dim dtmResult As Date

dtmResult = #1/1/100#
For Each varWork In Values
If varWork > dtmResult Then
dtmResult = varWork
End If
Next varWork

MaxDate = dtmResult

End Function

Example of use in query, using Orders table from Northwind ...

SELECT Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
MaxDate([OrderDate],[RequiredDate],[ShippedDate]) AS Expr1
FROM Orders

--
Brendan Reynolds

I have a query that has 7 date fields, I've used an inner join query to
create and due to the nature of my data, I need to have the 7 date
fields
as
they are. I now need an additional field in the query that shows the
earliest date. I know I can do a nested Iif statement, but I think it
will
be too long to fit in an expression. Can anyone help me with some
code
that
would achieve this? Thank you.

 

[John Spencer]

Good solution as long as the poster doesn't need the rows. I guess you could
use an IIF statement in the Select clause if you still needed the row returned.

IIF([Date1] is Not Null and [Date2] is Not Null, MaxDate(...), Null)

Or, perhaps, add a criteria to the query to exclude those records ....

WHERE ([Date1] IS NOT NULL) OR ([Date2] IS NOT NULL) OR ... etc.

--
Brendan Reynolds

I would try changing the first line to

Public Function MaxDate(ParamArrayValues()) as Variant
...

And changing MaxDate = dtmResult to

If DtmResult = #/1/1/100# then
MaxDate = Null
Else
MaxDate = dtmResult
End If

End Function

Thank you both for your help. I'm weighing my options, but have one
question. If use the code from Brendan I get #error in the rows where
all of
my dates are null. How can I change the code to leave the MaxDate blank
if
all of the date fields in that row are blank? Thanks.

:

Public Function MaxDate(ParamArray Values()) As Date

Dim varWork As Variant
Dim dtmResult As Date

dtmResult = #1/1/100#
For Each varWork In Values
If varWork > dtmResult Then
dtmResult = varWork
End If
Next varWork

MaxDate = dtmResult

End Function

Example of use in query, using Orders table from Northwind ...

SELECT Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
MaxDate([OrderDate],[RequiredDate],[ShippedDate]) AS Expr1
FROM Orders

--
Brendan Reynolds

I have a query that has 7 date fields, I've used an inner join query to
create and due to the nature of my data, I need to have the 7 date
fields
as
they are. I now need an additional field in the query that shows the
earliest date. I know I can do a nested Iif statement, but I think it
will
be too long to fit in an expression. Can anyone help me with some
code
that
would achieve this? Thank you.