Help with calculated field 1

[Advisedwolf]

Hi,

Am trying to write an If statement in a text box on a report.

If a field matches a certain criteria, then count the records.
I could easily do this in a query, but for other reasons, I would prefer to get this to work in the report itself.
My expression doesn't work, as it always displays a count of all the records, its not picking up only the open or re-opened records .. can someone assist me here please
the expression is:

=IIf([STATUS OF RETURN IN PURCHASING]="OPEN" or "re-opened",(Count([INVOICE NUMBER])))

Can anyone see what I have wrong is this please

Thanks

 

[THWatson]

Advisedwolf
Have you tried...
=IIf([STATUS OF RETURN IN PURCHASING]="OPEN" or [STATUS OF RETURN IN PURCHASING]="re-opened",Count([INVOICE NUMBER]),0)

I think you might have to insert the [STATUS OF RETURN IN PURCHASING] twice. Also, you didn't have a "False" part of the IIf expression; that's why I put in the 0.

Anyway, try it.

Tom

 

[Advisedwolf]

Thanks Tom, However, it still produces the full amount of records,

Any ideas, don't know if you need any further info off me, but I can state this report is being driven directly off the main source data table (only one table), and the field name is that as mentioned. Doesn't seem the most complex of ones to me, but i'll be damned if i can get it working!
Thanks for your assistance.

 

[THWatson]

Do you have the expression in the report Footer?

Tom

 

[Advisedwolf]

in the report header.

 

[THWatson]

Okay, here's a method you can use...

In the Detail section, put a new text box with the expression =IIf([STATUS OF RETURN IN PURCHASING]="OPEN" Or [STATUS OF RETURN IN PURCHASING]="re-opened",1,0) and make it a Running Sum Overall. Let's, for sake of example, call it txtStatus.

Then in the report Footer, put a text box with the expression =txtStatus

If you like the approach, you can make the txtStatus box in the Detail section "not visible"

But the text box where you pick up the final tally has to be in the Footer.

Tom

 

[Advisedwolf]

lovely, I see where your going with that one and it works in the footer.. i get the correct value of 428 records as opposed to the complete 865. I tried referencing the text box in the header to the footer (txtstatus), the header then producing a result of "0" .. is there no way to reference this header to the footer box?
But thats great! Thanks for your help Tom!

Think thats worth a purple star

 

[Advisedwolf]

sorry, worded that last one wrong.... "I tried referencing the text box in the header to the footer (txtstatus)," --- sorry, Not textstatus, but the name of the box in the footer...

Ta

 

[THWatson]

Well, the problem is that it doesn't work in the Header because Access hasn't yet calculated the total, so it has to go in the Footer.

Tom

 

[Advisedwolf]

ok great. will go with that then... at least it'll mean someone will have to actually read my report before getting to the summary's

Ta!

Have a great New Year!

 

[THWatson]

Sounds like a plan! And Happy New Year to you too!
Tom