Largest Date!

M

Mike

Group

I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.

'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003

I think what I have to do is to create a function to do this. Maybe using a
case statement this is what i'm thinking below. I think this code will work
but I need to make it a function. I would like my function to be named
LargestDateOf4 and have the following syntax <"maybe wrong term here"
date1,date2,date3,date4. Then I would go into my query and call the function
up assigning date1 equal to a field in a table etc....

Thanks Mike Sundman
mike( d o t )sundman( a t )ctd1.( c o m )

Dim dtmLargestDate as Date
Dim varDate1 as Date
Dim varDate2 as Date
Dim varDate3 as Date
Dim varDate4 as Date
Dim Max1 as Date
Dim Max2 as Date
Dim dtmLargestDate as Date

'Tests data and enters place holder of 01/01/1901

IF isdate(date1) = False Then
varDate1 = #01/01/1901#
Else
varDate1 = date1
End If
IF isdate(date2) = False Then
varDate2 = #01/01/1901#
Else
varDate2 = date2
End If
IF isdate(date3) = False Then
varDate3 = #01/01/1901#
Else
varDate3 = date3
End If
IF isdate(date4) = False Then
varDate4 = #01/01/1901#
Else
varDate4 = date4
End If
'Compares Max1 and Max2 to return largest date.
If varDate1 > varDate2 then
Max1 = varDate1
Else
Max1 = varDate2
End If
If varDate3 > varDate4 then
Max2 = varDate3
Else
Max2 = varDate4
End If
If Max1 > Max2 then
dtmLargestDate = Max1
Else
dtmLargestDate = Max2
End If
End Function














End If
 
A

Allen Browne

Mike, the long-term solution is to rebuild your tables.
Repeating fields like that break the most basic rule of data normalization.
More information:
http://databases.about.com/library/weekly/aa081901a.htm

If you must continue with the bad structure, you can use ParamArray to get a
function to accept an unknown number of arguments and choose the maximum
value. The function would look like this:

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function


In your query, you could then type a calculated field like this:
MaxDate: Largest([date1], [date2], [date3], [date4])
 
A

Alan

be warned- whilst the contents of the article mentioned
may or may not be good- do not attempt to navigate into
the Access link or you will be caught in a software
installation trap!!
-----Original Message-----
Mike, the long-term solution is to rebuild your tables.
Repeating fields like that break the most basic rule of data normalization.
More information:
http://databases.about.com/library/weekly/aa081901a.htm

If you must continue with the bad structure, you can use ParamArray to get a
function to accept an unknown number of arguments and choose the maximum
value. The function would look like this:

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function


In your query, you could then type a calculated field like this:
MaxDate: Largest([date1], [date2], [date3], [date4])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Group

I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed
Click to expand...
results. Most of the
dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.

'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003

I think what I have to do is to create a function to do
Click to expand...
this. Maybe using
a
case statement this is what i'm thinking below. I think
Click to expand...
this code will
work
but I need to make it a function. I would like my function to be named
LargestDateOf4 and have the following syntax <"maybe wrong term here"
date1,date2,date3,date4. Then I would go into my query
Click to expand...
and call the
function
up assigning date1 equal to a field in a table etc....

Thanks Mike Sundman
mike( d o t )sundman( a t )ctd1.( c o m )

Dim dtmLargestDate as Date
Dim varDate1 as Date
Dim varDate2 as Date
Dim varDate3 as Date
Dim varDate4 as Date
Dim Max1 as Date
Dim Max2 as Date
Dim dtmLargestDate as Date

'Tests data and enters place holder of 01/01/1901

IF isdate(date1) = False Then
varDate1 = #01/01/1901#
Else
varDate1 = date1
End If
IF isdate(date2) = False Then
varDate2 = #01/01/1901#
Else
varDate2 = date2
End If
IF isdate(date3) = False Then
varDate3 = #01/01/1901#
Else
varDate3 = date3
End If
IF isdate(date4) = False Then
varDate4 = #01/01/1901#
Else
varDate4 = date4
End If
'Compares Max1 and Max2 to return largest date.
If varDate1 > varDate2 then
Max1 = varDate1
Else
Max1 = varDate2
End If
If varDate3 > varDate4 then
Max2 = varDate3
Else
Max2 = varDate4
End If
If Max1 > Max2 then
dtmLargestDate = Max1
Else
dtmLargestDate = Max2
End If
End Function
Click to expand...


.
Click to expand...
 
M

Mike

Allen

There is no unknown quantities of dates in this table only four - they
aren't repeating. Is this a normalized table? If not what would I do to make
it normalized? The four track timed events each of them unique to a given
shop order number or job number. The only reason that i need to return the
largest date is to do a datediff("d",largestdate, dtmSoActualShipDate)
calculation to determine how many days late or early a job is. My function
below did work. Would there be any way to improve the function though? See
function with >> in front of it.
Query1 strShopOrderNumber dtmSoManualProjectedShipDate
dtmSoEventEstimation dtmSoEventActual dtmWishDate dtmSoActualShipDate
4412
10/27/2003 10/8/2003
10/27/2003
4459
12/1/2003

12/8/2003
4463
12/3/2003 12/8/2003 12/15/2003 12/20/2003
4465
12/5/2003 12/15/2003 12/30/2003 1/5/2004
4423 11/10/2003 1/4/2004





Allen Browne said:
Mike, the long-term solution is to rebuild your tables.
Repeating fields like that break the most basic rule of data normalization.
More information:
http://databases.about.com/library/weekly/aa081901a.htm

If you must continue with the bad structure, you can use ParamArray to get a
function to accept an unknown number of arguments and choose the maximum
value. The function would look like this:

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function


In your query, you could then type a calculated field like this:
MaxDate: Largest([date1], [date2], [date3], [date4])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Group

I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.

'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003

I think what I have to do is to create a function to do this. Maybe
Click to expand...
using
a
case statement this is what i'm thinking below. I think this code will work
but I need to make it a function. I would like my function to be named
LargestDateOf4 and have the following syntax <"maybe wrong term here"
date1,date2,date3,date4. Then I would go into my query and call the function
up assigning date1 equal to a field in a table etc....

Thanks Mike Sundman
mike( d o t )sundman( a t )ctd1.( c o m )

Dim dtmLargestDate as Date
Dim varDate1 as Date
Dim varDate2 as Date
Dim varDate3 as Date
Dim varDate4 as Date
Dim Max1 as Date
Dim Max2 as Date
Dim dtmLargestDate as Date

'Tests data and enters place holder of 01/01/1901

IF isdate(date1) = False Then
varDate1 = #01/01/1901#
Else
varDate1 = date1
End If
IF isdate(date2) = False Then
varDate2 = #01/01/1901#
Else
varDate2 = date2
End If
IF isdate(date3) = False Then
varDate3 = #01/01/1901#
Else
varDate3 = date3
End If
IF isdate(date4) = False Then
varDate4 = #01/01/1901#
Else
varDate4 = date4
End If
'Compares Max1 and Max2 to return largest date.
If varDate1 > varDate2 then
Max1 = varDate1
Else
Max1 = varDate2
End If
If varDate3 > varDate4 then
Max2 = varDate3
Else
Max2 = varDate4
End If
If Max1 > Max2 then
dtmLargestDate = Max1
Else
dtmLargestDate = Max2
End If
End Function
Click to expand...
Click to expand...
 
A

Alan

Yes, I am warning about the attempted automated download
of software- I didn't stop to see what software it was as
it was the usual type of set up where no action you take
will prevent the loop until you shut down IE from the Task
Manager. My experience has been "dally too long and get
the software anyway!" For all I know it could be benign,
but I object to this procedure in any case.

The link that causes it is seen by scrolling down the left
hand column "subjects" where you will find Access and
various others.
-----Original Message-----
Alan, can you clarify your warning please?

Which "Access" link is a problem?
Are you suggesting software is automatically installed by the site?

Alan said:
be warned- whilst the contents of the article mentioned
may or may not be good- do not attempt to navigate into
the Access link or you will be caught in a software
installation trap!!

http://databases.about.com/library/weekly/aa081901a.htm
Click to expand...
[snip]


.
Click to expand...
 
M

Mike

Query1

strShopOrderNumber dtmSMPSD dtmSEE dtmSEA dtmWD dtmSASD
4412 10/27/2003
10/27/2003
4459 1/01/2001
1/20/2003


The data came through ugly. Anyway here is an example of the table data.

Regards, Mike
 
A

Allen Browne

Hi Mike

You may have a reason for the dates, and perhaps the names you used were
just examples. Looking at the names suggest making a related table with
fields:
- ForeignID indicates which row of the main table this applies
to;
- WhatKindOfDate something that tells whatever is the difference between
the 4;
- TheDate the value of this date
With that structure, you could use DMax() to get the most recent date
matching the key value.

If you want to retain you structure and pass in the 4 dates, you can use the
function I provided. The fact that it also works with more (or fewer)
arguments is meant to be a bonus. And because the dates are in an array, the
code is much more efficient to write than having to handle each instance
individually.

Hope that helps

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Allen

There is no unknown quantities of dates in this table only four - they
aren't repeating. Is this a normalized table? If not what would I do to make
it normalized? The four track timed events each of them unique to a given
shop order number or job number. The only reason that i need to return the
largest date is to do a datediff("d",largestdate, dtmSoActualShipDate)
calculation to determine how many days late or early a job is. My function
below did work. Would there be any way to improve the function though? See
function with >> in front of it.
Query1 strShopOrderNumber dtmSoManualProjectedShipDate
dtmSoEventEstimation dtmSoEventActual dtmWishDate dtmSoActualShipDate
4412
10/27/2003 10/8/2003
10/27/2003
4459
12/1/2003

12/8/2003
4463
12/3/2003 12/8/2003 12/15/2003 12/20/2003
4465
12/5/2003 12/15/2003 12/30/2003 1/5/2004
4423 11/10/2003 1/4/2004





Allen Browne said:
Mike, the long-term solution is to rebuild your tables.
Repeating fields like that break the most basic rule of data normalization.
More information:
http://databases.about.com/library/weekly/aa081901a.htm

If you must continue with the bad structure, you can use ParamArray to
Click to expand...
get
a
function to accept an unknown number of arguments and choose the maximum
value. The function would look like this:

Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function


In your query, you could then type a calculated field like this:
MaxDate: Largest([date1], [date2], [date3], [date4])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Group

I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to
Click to expand...
Click to expand...
be
an using
Click to expand...
Click to expand...
 
M

Mike Sherrill

I have a table with four fields that contain date data. I need to
compare and determine which one of the four dates is the largest. I have
tried to do some iif() statements but with mixed results. Most of the dates
work out of 158 records only 21 don't work. I thought I would recheck the
data so I went to the table and Reentered it. I figured there has to be an
easier way of doing this. Below is an outline of what I need to do.

'below is a fake example of the data I'm using. Showing the null problem.
date 1 date 2 date 3 date 4
"null" 12/05/2000 01/09/2001 5/04/2003
Click to expand...

SELECT PrimaryKey, "Date1" AS DateColumnName, Date1 AS DateColumn
FROM YourTableName
UNION
SELECT PrimaryKey, "Date2" AS DateColumnName, Date2 AS DateColumn
FROM YourTableName
UNION
SELECT PrimaryKey, "Date3" AS DateColumnName, Date3 AS DateColumn
FROM YourTableName
UNION
SELECT PrimaryKey, "Date4" AS DateColumnName, Date4 AS DateColumn
FROM YourTableName;

Save that query. That should give you a query you can use to find the
max date, using

SELECT PrimaryKey, Max(DateColumn) AS MaxOfDateColumn
FROM YourQueryName
GROUP BY PrimaryKey;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

call function module from query - having much trouble 8
Macro to sort 1
Filter by date range for any one of three dates. 0
What am I doing wrong? 5
If/Then/Else Query 6
formatting cells based on comparison 1
Calculating chnages in time 1
Initializing a user-defined type 1

Top