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)

 

[DoogieB]

Sorry I don't have more time to help you on this, but look up in the help files the DLookup function. You will probably be able to do what you need to with this.

 

[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

 

[CosmoKramer]

Jim,

See if this previous thread helps - thread703-145641

 

[jamesaarnold]

I don't get it? This did nothing for me! As I stated I'm Already using the code to get 14 days past due,

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

It works Great.

Now I need to be able to create duplicate fields on my report for 14 days past due records (the output of the expression above).

One Field needs to give me a Y-T-D (year-to-date)count on the 14 day past due records.

The other field needs to give me M-T-D (month-to-date) count on the 14 days past due records


Can Anybody Please Help!!


Jim

 

[CosmoKramer]

You previously said you wanted to sort by MTD & YTD. Now you want to count those records??

Providing you have the detail records, you should be able to group on month and group on year. Add an invisible unbound text box with its Control source set to =1 and RunningSum to Yes. Set that text box as the control source of another text box in your group footer(s) and that will give you the counts......

 

[jamesaarnold]

I apologize. I do want to do a count. I need one field with a count of ytd records, and another with month to date records. I just tried your recommendation and it gave me an output of 1 in the field I desginated. One interesting thing it did, when I was setting the running sum, it did not give me a "yes" option. It gave me "no", "over group", or "over all". I tried all three with the same results. An output of 1. I am using dummy data right now. I have put 5 records in the database, and 2 are 14 days past due. My output should have been 2.

Thanks again!

Jim

 

[CosmoKramer]

My turn to apologize....RunningSum should be set to Over Group.

I just want to make sure - Say you called your invisible text box txtCount, you are putting =[txtCount] in the control source of the text box in your footer, right??

 

[jamesaarnold]

yes, actually it was =[TXT204}, Just for grins I just tried it again and got the same results. Thanks again, I'll keep pecking away!



Jim

 

[jamesaarnold]

=(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)

 

[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