compariing and listing certain dates

[Lee]

I am using Access 2003 and I have a database that lists vision date, hearing
date and consent date on individuals. I would like to develop a query that
will compare those three dates and provide the lastest date and list it as
"Date Ready" column. For example, I have John Smith, his hearing date
10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the last
date (in this case, 12/1/06) to show up as "Date Ready". I know that I can
use Date Ready[query]), but I don't know how to compare the dates and
select the "latest" date.

 

[John Spencer]

And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming
that all three dates must be filled in, the logic would be

DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(A<B,
IIF(B<C,C,B),IIF(A<C,C,A)),Null)

Replace A, B, and C with the names of your fields.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dale Fye]

Lee,

When I want to compare the values in multiple fields, I use a function I
wrote that will accept any number of values and return the maximum from among
these values. It works with dates, strings, or numbers. My function ignores
Null values, so if one of the fields is NULL, it just ignores is. But you
could modify it so that if any of the elements were NULL, it would return a
value or a NULL value, like below. You might want to change the name of the
function to fnDateReady for your purposes.

Public Function fnMaximum(ParamArray MyArray()) As Variant

Dim intLoop As Integer

fnMaximum = Null
For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
fnMaximum = NULL
Exit Function
ElseIf IsNull(fnMaximum) Then
fnMaximum = MyArray(intLoop)
ElseIf MyArray(intLoop) > fnMaximum Then
fnMaximum = MyArray(intLoop)
End If
Next intLoop

End Function

HTH
Dale

 

[Lee]

thank you that helped. I also tried to enter another date and I had to use
the original query and build another query using that query. Is it possible
to use 4 dates without having to build a query on a query? For example: I
have A (MRT Date), B(Vision Date), C (HEaring Date), D (Consent Date) dates
and I used the IIf(A<B,IIf(B<C,C,B), IIF(A<C,C,A)),Null) and then labeled
that query "Ready", when I tried to add 'D' date, I had to use IIF
([ready]<[Consent Date],[Consent Date],[Ready]) AS [Date Ready]. I was
thinking IIf([MRT date] Is Not Null And [vision date] Is Not Null And
[hearing date] Is Not Null And [consent date] Is Not Null,IIf([MRT
date]<[vision date],IIf([vision date]<[hearing date],IIf([hearing
date]<[consent date],[consent date],[hearing date]),IIf([mrt date]<[consent
date],[consent date],[mrt date])),Null)) AS [date ready] - but it didn't
work. The query built on a query worked.

And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming
that all three dates must be filled in, the logic would be

DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(A<B,
IIF(B<C,C,B),IIF(A<C,C,A)),Null)

Replace A, B, and C with the names of your fields.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am using Access 2003 and I have a database that lists vision date,
hearing
date and consent date on individuals. I would like to develop a query
that
will compare those three dates and provide the lastest date and list it as
"Date Ready" column. For example, I have John Smith, his hearing date
10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the
last
date (in this case, 12/1/06) to show up as "Date Ready". I know that I
can
use Date Ready[query]), but I don't know how to compare the dates and
select the "latest" date.

 

[John Spencer]

What you need to do is chage the structure of your tables. It seems
that you should have an additional table with a field that stores the
value of the primary key in your current table, a field to store the
event date, and another field to store the event type.

With that structure, getting the latest date is fairly simple even if
you have 50 events associated with your main table.

As posted elsewhere, if you are going to keep the current structure,
then you will probably be better off writing a VBA function that does
what you need.

Using IIF can get fairly complex. You might use the switch function
instead, but every time you add another date, you are going to have to
modify this in the query.

SWITCH(A Is Null Or B is Null Or C is Null or D is Null, Null,
A>=B and A>=C and A>=D, A,
B>=C and B>=D, B,
C>=D, C,
True, D)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

thank you that helped. I also tried to enter another date and I had to use
the original query and build another query using that query. Is it possible
to use 4 dates without having to build a query on a query? For example: I
have A (MRT Date), B(Vision Date), C (HEaring Date), D (Consent Date) dates
and I used the IIf(A<B,IIf(B<C,C,B), IIF(A<C,C,A)),Null) and then labeled
that query "Ready", when I tried to add 'D' date, I had to use IIF
([ready]<[Consent Date],[Consent Date],[Ready]) AS [Date Ready]. I was
thinking IIf([MRT date] Is Not Null And [vision date] Is Not Null And
[hearing date] Is Not Null And [consent date] Is Not Null,IIf([MRT
date]<[vision date],IIf([vision date]<[hearing date],IIf([hearing
date]<[consent date],[consent date],[hearing date]),IIf([mrt date]<[consent
date],[consent date],[mrt date])),Null)) AS [date ready] - but it didn't
work. The query built on a query worked.

And what do you want to do if 0, 1 or 2 of the dates is filled in? Assuming
that all three dates must be filled in, the logic would be

DateReady: IIF(A is Not Null and B is Not Null and C is Not Null,IIF(A<B,
IIF(B<C,C,B),IIF(A<C,C,A)),Null)

Replace A, B, and C with the names of your fields.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am using Access 2003 and I have a database that lists vision date,
hearing
date and consent date on individuals. I would like to develop a query
that
will compare those three dates and provide the lastest date and list it as
"Date Ready" column. For example, I have John Smith, his hearing date
10/20/06, his vision date 11/20/06 and consent date 12/1/06. I need the
last
date (in this case, 12/1/06) to show up as "Date Ready". I know that I
can
use Date Ready[query]), but I don't know how to compare the dates and
select the "latest" date.