Maximum of Two Dates

[Duncs]

I have two deparate date fileds, and I need to know the maximum of the
two. This date is then appended to a table.

So, existing table has a Product Code & a Date field.

A query is run against a second table that identifies, for each
product code, what the latest date is between the last sales date &
the reorder date. So, if the data were:

Product Code Sales Date ReOrder Date
15985GH 01/08/09 15/08/09

The first table would show:

Product Code MaxDate
15985GH 15/08/09

If the dates were reversed above, it should still show the 15/08/09 as
the max date.

Any help would be gratefully appreciated.

TIA

Duncs

 

[Roger Carlson]

First of all, I have to point out that if you'd designed the table
differently, it would be much easier. For instance, if you'd had just one
field for the date and another field to designate the type of date, like
this:

Product Code Order Date Order Type
15985GH 01/08/09 Sale
15985GH 15/08/09 ReOrder

you could use a simple SQL statement:

INSERT INTO YourTable2 ( Product Code, MaxDate )
SELECT YourTable1.[Product Code], Max(YourTable1.[Order Date]) AS
[MaxOfOrder Date]
FROM YourTable1
GROUP BY YourTable1.[Product Code];

However, with the design you've got, you're going to have to use an IIF
statement to find the max date.

INSERT INTO YourTable2 ( [Product Code], MaxDate )
SELECT YourTable1.[Product Code], IIf([Sales Date]>=[ReOrder Date],[Sales
Date],[ReOrder Date]) AS MaxDate
FROM YourTable1;

Now, this doesn't sound like a big deal, and so far it isn't. But if you
ever need to add additional fields like ReReOrder Date or something, the
first query will work unmodified, whereas the second will need an additional
condition for each new field added. In time, this can become a real mess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Here is a function that will handle that for you. If both dates are Null, it
returns Null. If one is Null and the other has a value, the date with the
value is returned. If both have values, the most recent date is returned.

Public Function MaxDate(DateOne As Variant, DateTwo As Variant) As Variant

If IsNull(DateOne) Then
If IsNull(DateTwo) Then
MaxDate = Null
Else
MaxDate = DateTwo
End If
ElseIf IsNull(DateTwo) Then
MaxDate = DateOne
ElseIf DateOne > DateTwo Then
MaxDate = DateOne
Else
MaxDate = DateTwo
End If

End Function

 

[Duncs]

Roger,

There is a certainty that there will be no other date fileds added /
compared, so your solution works OK.

Many thanks

Duncs

First of all, I have to point out that if you'd designed the table
differently, it would be much easier.  For instance, if you'd had just one
field for the date and another field to designate the type of date, like
this:

 Product Code     Order Date     Order Type
 15985GH            01/08/09        Sale
 15985GH            15/08/09        ReOrder

you could use a simple SQL statement:

INSERT INTO YourTable2 ( Product Code, MaxDate )
SELECT YourTable1.[Product Code], Max(YourTable1.[Order Date]) AS
[MaxOfOrder Date]
FROM YourTable1
GROUP BY YourTable1.[Product Code];

However, with the design you've got, you're going to have to use an IIF
statement to find the max date.

INSERT INTO YourTable2 ( [Product Code], MaxDate )
SELECT YourTable1.[Product Code], IIf([Sales Date]>=[ReOrder Date],[Sales
Date],[ReOrder Date]) AS MaxDate
FROM YourTable1;

Now, this doesn't sound like a big deal, and so far it isn't.  But if you
ever need to add additional fields like ReReOrder Date or something, the
first query will work unmodified, whereas the second will need an additional
condition for each new field added.  In time, this can become a real mess.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:

I have two deparate date fileds, and I need to know the maximum of the
two.  This date is then appended to a table.

So, existing table has a Product Code & a Date field.

A query is run against a second table that identifies, for each
product code, what the latest date is between the last sales date &
the reorder date.  So, if the data were:

Product Code     Sales Date     ReOrder Date
15985GH            01/08/09        15/08/09

The first table would show:

Product Code     MaxDate
15985GH            15/08/09

If the dates were reversed above, it should still show the 15/08/09 as
the max date.

Any help would be gratefully appreciated.

Duncs- Hide quoted text -

- Show quoted text -

 

[Duncs]

Dave,

Many thanks for that.

I've used the query supplied by Dave in the previous post, but I can
see a few places where I can use yours.

Many thanks for this.

Duncs