Using a count(IIF) statement using a textbox variable

[Triacona]

Dear all,

My problem is the following...
I have a report which calculates how many days it takes to respond to a letter.

We have a reception date field: RCPTND

We have an acknowledgement date field: ACKNMD

I then have a textbox which I have named:TtlAckDays

This textbox (TtlAckDays) calculates the time taken by this code:

=[ACKNMD]-[RCPTND]

I now want to count the number of instances the reponses have been sent out within 7 days.

I have another textbox: NoWithin7Days to count the number of instances the reponses have been sent out within 7 days.

This is textbox: NoWithin7Days code:

=Count(IIf([TtlAckDays]<=7,0))

As you can see from the above I am trying to call the value within the textbox: TtlAckDays and use it to count the number of responses, responded to within 7 days.

I run the report and get asked to input TtlAckDays...

Why is this??

Can I pull values from other textboxes?

Should I rather create a new field in the query?

I would prefer to do it within the report though.

Any help will be greatly appreciated

Kind regards

Triacona

 

[dhookom]

You can't aggregate a text box from the detail section into another section. You can possibly use and expression that references only fields from the report's record source:

  =Sum(Abs( ([ACKNMD]-[RCPTND])<=7))

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thanks for your help

I am using this in the detail section of the report, and it is giving me the total number of reponses in 7 days, instead of just the current amount.

I have modified my query with an expressions:

TtlDaysAckn: [ACKNMD]-[RCPTND]
NumberResponded7Days: Count(IIf([TtlDaysAckn]<=7,0))

This works and I am getting the correct figures.

I have tried something similar in the report...
I have created another textbox (Resp7Day) and in properties -> control source I inputed

=TtlAckDays

which contains the value after the calculation[ACKNMD]-[RCPTND] , I then used the count(iif) statement and this still didn't work??

Is it just not possible to manipulate calculated data in a reports details section?


Any help would be greatly appreciated

Kind regards

Triacona

 

[dhookom]

As I stated "You can possibly use and expression that references only fields from the report's record source".

Don't use Count() since Count() counts and only ignores null. That's why I used Sum().

Also, IIf() should have 3 arguments, not 2.

Duane
Hook'D on Access
MS Access MVP