Latest of several dates

[Guest]

I've researched here, but nothing seems to quite match my scenario. What I
have is 8 separate date fields used as a lease prospect is followed up on.
They are "followupdt1" thru 8. They will always use followupdt1, but no
guarantee that they will use the rest. I am looking for a way to retrieve
only the latest date for a report. In Excel this is simple using
Max(A1,B1,C1). Not so simple, though, in Access.
Thanks

 

[Duane Hookom]

Excel is a spreadsheet and Access is a relational database. I would remove
the followup date fields and create a related table where each followup date
for a prospect creates its own record. You could then use a simple totals
query to find the max followup date for each prospect.

If you can't or won't normalize your table, you can create a union query
that normalizes. You can then create a totals query based on the union
query.

 

[Brendan Reynolds]

I agree fully with Duane's advice elsewhere in this thread, that the best
solution is, if possible, to change the database design. Because there are
times when we are forced to deal with non-normalized data from 'legacy' data
sources, here's an example of how you could achieve the desired result with
the existing database design. But I would not recommend doing it except as a
last resort - the queries are tedious to write, and inefficient in use, as
they can not make use of any indexes that might exist on these fields.

The solution requires VBA code such as this in a standard module ...

Public Function FMax(ParamArray Values() As Variant) As Variant

Dim varResult As Variant
Dim varLoop As Variant

varResult = Null
For Each varLoop In Values
If IsNull(varResult) Or (varLoop > varResult) Then
varResult = varLoop
End If
Next varLoop

FMax = varResult

End Function

You can now call this function in a query like this ..

SELECT tblTest.TestDate1, tblTest.TestDate2, tblTest.TestDate3,
FMax([TestDate1],[TestDate2],[TestDate3]) AS TestFMax
FROM tblTest;

 

[Guest]

I understand your point and agree with both of you. I had created the table
originally, but wasn't sure how to work with it unless I create a subform.
They do need to see all the dates in order to note the timeframes between
them. (There are specified callback timeframes that should be being met.)
So all the dates are shown on both the form and another report. Thinking
about it now, a subform would allow me to have as few or as many dates as
needed, all using one name, i.e., followupdt. Please let me know if I am on
the right track in my thinking or there is something better I could be doing.
Thanks for your help!

I agree fully with Duane's advice elsewhere in this thread, that the best
solution is, if possible, to change the database design. Because there are
times when we are forced to deal with non-normalized data from 'legacy' data
sources, here's an example of how you could achieve the desired result with
the existing database design. But I would not recommend doing it except as a
last resort - the queries are tedious to write, and inefficient in use, as
they can not make use of any indexes that might exist on these fields.

The solution requires VBA code such as this in a standard module ...

Public Function FMax(ParamArray Values() As Variant) As Variant

Dim varResult As Variant
Dim varLoop As Variant

varResult = Null
For Each varLoop In Values
If IsNull(varResult) Or (varLoop > varResult) Then
varResult = varLoop
End If
Next varLoop

FMax = varResult

End Function

You can now call this function in a query like this ..

SELECT tblTest.TestDate1, tblTest.TestDate2, tblTest.TestDate3,
FMax([TestDate1],[TestDate2],[TestDate3]) AS TestFMax
FROM tblTest;

--
Brendan Reynolds (MVP)

I've researched here, but nothing seems to quite match my scenario. What
I
have is 8 separate date fields used as a lease prospect is followed up on.
They are "followupdt1" thru 8. They will always use followupdt1, but no
guarantee that they will use the rest. I am looking for a way to retrieve
only the latest date for a report. In Excel this is simple using
Max(A1,B1,C1). Not so simple, though, in Access.
Thanks

 

[Josh Nankivel via AccessMonster.com]

You may be able to run run a crosstab query on just the date fields, and
find the max for that. Alternatively, if you just want this date to show
up on a report, you could also put all the date controls into a report and
set the visible property to no, and then use a max calculation as you would
in excel.

 

[Brendan Reynolds]

It sounds to me like you are on the right track, yes.

--
Brendan Reynolds (MVP)

I understand your point and agree with both of you. I had created the
table
originally, but wasn't sure how to work with it unless I create a subform.
They do need to see all the dates in order to note the timeframes between
them. (There are specified callback timeframes that should be being met.)
So all the dates are shown on both the form and another report. Thinking
about it now, a subform would allow me to have as few or as many dates as
needed, all using one name, i.e., followupdt. Please let me know if I am
on
the right track in my thinking or there is something better I could be
doing.
Thanks for your help!

I agree fully with Duane's advice elsewhere in this thread, that the best
solution is, if possible, to change the database design. Because there
are
times when we are forced to deal with non-normalized data from 'legacy'
data
sources, here's an example of how you could achieve the desired result
with
the existing database design. But I would not recommend doing it except
as a
last resort - the queries are tedious to write, and inefficient in use,
as
they can not make use of any indexes that might exist on these fields.

The solution requires VBA code such as this in a standard module ...

Public Function FMax(ParamArray Values() As Variant) As Variant

Dim varResult As Variant
Dim varLoop As Variant

varResult = Null
For Each varLoop In Values
If IsNull(varResult) Or (varLoop > varResult) Then
varResult = varLoop
End If
Next varLoop

FMax = varResult

End Function

You can now call this function in a query like this ..

SELECT tblTest.TestDate1, tblTest.TestDate2, tblTest.TestDate3,
FMax([TestDate1],[TestDate2],[TestDate3]) AS TestFMax
FROM tblTest;

--
Brendan Reynolds (MVP)

I've researched here, but nothing seems to quite match my scenario.
What
I
have is 8 separate date fields used as a lease prospect is followed up
on.
They are "followupdt1" thru 8. They will always use followupdt1, but
no
guarantee that they will use the rest. I am looking for a way to
retrieve
only the latest date for a report. In Excel this is simple using
Max(A1,B1,C1). Not so simple, though, in Access.
Thanks

 

[Guest]

Just a quick note to say thanks. I wrote the date(s) into a subform in a
single field, created a small query to get the Max and and then added that to
my report query. Worked like a charm and way too easy for as hard as I was
making it. As always, I appreciate the input!