Help with report fields

[jamesaarnold]

I have created a database with a normal date field. In my report one of my fields needs to do a count on how many records are older then 14 days (past due). How do I have this field populated automaticlly on my report. I have been able to get sum, and dcount attributes to do totals, but the 14 day thing has me verrrry confused. In addition to this I have to create duplicate fields for (past due) that tally by month-to-date, and year-to-date. Any help would be greatly appreciated.

Jim (newbie)

 

[llocklin]

My first thought is to base the report on a query and create the calc'd field there. An example calculated field might be: PastDue: iif(now() - DueDate > 14, True, False). Use a totals query and count the True values. This probably isn't the optimal method, but it'll work.

Luther

 

[jamesaarnold]

I figured out the proper way.

=Dcount("[TRACKING NUMBER]", "[DAILY LINE UP]","DateDiff('d'[DueDate],Now()) > 14&quot

But now I'm stuck on how to get these same records sorted by month-to-date, and year-to-date on my reports. Any Ideas?????????

Jim

 

[jamesaarnold]

This is the expression I'm trying to use.

=(DCount("[TRACKING NUMBER]","DAILY LINE UP","DateDiff('d',[DATE],Now()) > 14&quot) & " - " & (DCount("[TRACKING
NUMBER]","DAILY LINE UP","[DATE]
Between Format('1/' & Month(Now) & '/' & Year(Now),'m/d/yyyy') And Now&quot)


TRACKING NUMBER = autonuber field/primary key
DAILY LINE UP = Table name
DATE = Date/Time Field

I still get errors when I try to run it. What do you think??????


Jim