Try the attached. I changed the 'Due' formula in Excel and added 'Overdue' (over 1 month) and 'Expired' (less than 1 month) sets to the report. Getting the dates to output in the same format as your workbook was the tricky part.
The three DATABSE fields are:
Overdue
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] < 0 ORDER BY [Last] " \l "9" \b "47" \h }
Expired
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = 0 ORDER BY [Last] " \l "9" \b "47" \h }
Due Within 1 Month
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept] , FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = 1 ORDER BY [Last]" \l "9" \b "47" \h }
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
|