Get two latest dates 1

[mardi326]

I thought I knew what I was doing, but evidently, I don't.
Here is the condensed version.

John Jones 1/3/2004
John Jones 12/8/2003
John Jones 10/3/2003
May Martin 2/1/2004
May Martin 1/9/2004
May Martin 12/3/2003

I need to pull the two latest dates of each person and calculate how many days are in between. Also, if there is no data for the current month, I need to be able to note that.

Any help would be appreciated.

 

[lbass]

For the first part of your question, group on {table.personID}, sort the report by {table.date} in descending order, and then create a formula:

if count({table.date},{table.personID}) >= 2 and
{table.date} = maximum({table.date}, {table.personID}) then
totext({table.date} - next({table.date}),0,"&quot

If you want to view the result in the group header, copy the formula into the group header. You don't have to use the totext function, but for groups where there are only one record, the empty result would probably be less confusing than a "0".

To note that there is no current data, create a formula like:

if month(maximum({table.date}, {table..personID})) <> month(currentdate) and
year(maximum({table.date}, {table.personID})) <> year(currentdate) then &quot;No Data in Current Month&quot;

If you only want to display the date differences if there is data during the current month, then you can add in a clause into the first formula that specifies that the month and year of the maximum date must equal the month/year of the currentdate else &quot;No Current Data&quot;.

-LB