Count IIF expression not wokring in access 2003 report (1 Viewer)

JWPratt8 · Aug 12, 2013

Hi All,

I am currently using Access 2003 and have an unbound text box in a report with the following code:

=Count(IIf([Complaints Table]![Month]=1,IIf([Complaints Table]![Decision - Our Favour?]="Y",0)))

The problem that I am experiencing is that I keep getting a "#Error" message in the text box when I run the report.

However, the puzzling thing is that I have an identical report in Access 2010 with the same text box and code which works perfectly. I am relatively new to access so i might be missing a trick here! If anyone could help that would be brilliant.

Thanks,

J.

pr2-eugin · Aug 12, 2013

Your IIF's are not complete.. The general IIf structure is..

Code:

iif ( condition, value_if_true, value_if_false )

If you are nesting multiple IIF, then make sure all arguments are filled in..

Code:

iif ( condition, iif ( condition, value_if_true, value_if_false )[COLOR=Red][B], value_if_false[/B][/COLOR] )

Your code is missing the False part of the first if.. as highlighted above..

JWPratt8 · Aug 12, 2013

Okay thanks.

So am I right in believing that the code should now be written as follows:

=Count(IIf([Complaints Table]!Month=1,IIf([Complaints Table]![Decision - Our Favour?]="N",0),0))

I have tried this and it still gives a "#Error".

pr2-eugin · Aug 12, 2013

Hmmm.. Try this..

Code:

= Count(IIf([Complaints Table]![Month] = 1, IIf([Complaints Table]![Decision - Our Favour?] = "N", Null), Null))

Month is a very bad field name.. If you are in any situation to change field names, I would advice you to change it NOW..

JWPratt8 · Aug 12, 2013

I still get a #Error message in the text box. But it's just really strange that it works perfectyl in my 2010 database but not in 2003.

I'm affraid I'm in no position to change the name of the "Month" field.

pr2-eugin · Aug 12, 2013

Well, still the IIF is wrong.. It does not have a False part for the Second IIF.. Try this..

Code:

= Count(IIf([Complaints Table]![Month] = 1, IIf([Complaints Table]![Decision - Our Favour?] = "N", 1, Null), Null))

JWPratt8 · Aug 12, 2013

I've ammended the expression but I still get the #Error message.

FYI the report is linked to a query that uses a paramter to search by the year if that changes anything.

I just can't understand why the code would work in Access 2010 & not in Access 2003.

pr2-eugin · Aug 12, 2013

Okay I think I have overlooked something here.. Is this a field in the Query or a control source in the Report?

If not in a Query, try using DCount..

Code:

= DCount("*", "[Complaints Table]", "[Month] = 1 AND [Decision - Our Favour?] = 'N'")

JWPratt8 · Aug 12, 2013

Works like a charm!

Thank you very much.

pr2-eugin · Aug 12, 2013

Glad that helped.. Good luck..

JWPratt8 · Aug 12, 2013

Actually after ammending all the text boxes in the report, they all only show 0. So, something is not quite right.

Sorry about that, I should have checked all of them before coming back to you!

pr2-eugin · Aug 12, 2013

Okay something is not right here..

JWPratt8 said:
Actually after ammending all the text boxes in the report, they all only show 0. So, something is not quite right.

What are you exactly trying to do? Why could you not add this field as part of the Query and just set the Control Source to this calculated field of the Query..

JWPratt8 · Aug 12, 2013

Sorry I'm relatively new to Access so I've probably created this report all wrong.

What I'm trying to do is have a yearly report that has a sum (or count in this case) of the total number a "Y" in the "Decision - Our Favour?" field and the total number on "N". I wanted to seperate this for each month.

I created the report using unbound text boxes in the report footer with the code we have been disscussing, this is because I couldn't figure out how to do it using a query.

I also have another report where I'm trying to have a text box that shows the most common word in a field over a given period, but again I can't figure out how to do that either.

I'm sure there is a proper way of constructing a report for how I want but I got it working to how I wanted it in Access 2010, but now I have to re-do the database in Access 2003 the same report does not work.

Thanks,

J.

Count IIF expression not wokring in access 2003 report (1 Viewer)

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

pr2-eugin

Super Moderator

JWPratt8

Registered User.

Similar threads

Users who are viewing this thread