Max Date Formula

[Adam_Alsop]

I 'm having more Date issues than I know what to do with, lol.

I have 2 Date fields in my table - and , there will be only one per record, but multiple - I need to group by , to get the totals by , but I also need to know when the last does not equal the Max, so I can get a Count of what records are no longer returned for the . I also need to get the Count of the records where the equals the Max in order to get the newly added records. All fields are DateTime. EX: Max{LastScanDate-03/26/2010} (value is for all records) {@For The Week Beginning} = DateAdd("d",DateDiff("d",#1/1/1900#,IF DayOfWeek({Table.DateField}, crMonday) = 1 THEN {Table.DateField} ELSE DateAdd("d",(1- (DayOfWeek({Table.DateField}, crMonday))), {Table.DateField})),#1/1/1900#) <> IF DayOfWeek(CurrentDate, crMonday) = 1 THEN CurrentDate ELSE DateAdd("d",(1- (DayOfWeek(CurrentDate, crMonday))), CurrentDate) Group1 - {DiscoveryDate=12/2009} Group2 - Path1 {DiscoveryDate=12/01/2009} {LastScanDate=12/01/2009} Path2 {DiscoveryDate=12/01/2010} {LastScanDate=01/01/2009} Path1 {DiscoveryDate=12/01/2009} {LastScanDate=02/01/2009} Path2 {DiscoveryDate=12/01/2009} {LastScanDate=02/01/2009} GroupFooter-2 DistinctCount of Path (should equal 2); DropOff = True (LastScanDate=02/01/2009 < Max)

DropOff = True (would require < Max()) ; DropOff = False (would require LastScanDate In [{@For The Week Beginning}, Max] As indicated above, the ReportFooter needs to have the totals of Group2 - DistinctCount of Path where =Max regardless of whether there were previous records for the Path, as well as DistinctCount of Path where =Max()

Complicated enough?

[Adam_Alsop]

I could even live with being able to get the count of servers that appeared in the previous month's scan but not in the current month's scan.

Preferably, I would like to get those values for each of the previous months - so it shows that say, 50 severs showed up in 12/09 that did not show up in 03/10; 20 servers that showed up in 01/10 that did not show up in 03/10, etc.

Thanks!

[Adam_Alsop]

Ok, disregard - db structure changed.