Nested IIF

[ChrisTheAncient]

Yes, I know I can't do that (as far as I can see). But I want to do it on a report in A97.

In a grouped report, I count the number of messages (in the detail section) each client has received that day. This value is put in the Group Header. The count is done simply on the message table field called [ID]. Each client usually has a different number of messages.

At the moment, I have a nice looking unbound text box in the Group Header with the following Control Source:

="On " & Format([Date],"dddd"", ""dd mmmm yyyy&quot & IIf((Count([MessageID])>1),(" there were " & Count([MessageID]) & " messages &quot,(" there was " & Count([MessageID]) & " message &quot)

This is fine as long as there has been at least one message!

My problem occurs if there have been no messages - I then get the good ol' #Error.

If I can't nest IIF, is there a way I can (easily) put up a "There were no messages today" legend.

I do have some fairly simple skills in vb - as long as I'm led very gently by the hand!

TIA

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[dhookom]

You should be able to use the [HasData] property of the report (simplified):
=IIf([HasData],"There are " & Count(*) & " records", "No Records in report&quot

Duane
MS Access MVP

 

[ChrisTheAncient]

Hi Duane

A new concept for me is this HasData! Thanks for the intro on that.

Unfortunately, your idea could give a screwed up message of "There are 1 messages today" - and being a perfectionist, I'm trying to overcome that.

Any more thoughts, folks?

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[PaulBricker]

As far as I know you can use a Nested IIf in the Control source for your textbox. I'm not sure what will happen when there are 0 records but this should work.

="On " & Format([Date],"dddd"", ""dd mmmm yyyy&quot & IIf(Count([MessageID])>1," there were " & Count([MessageID]) & " messages ", IIf(Count([MessageID]=1)," there was 1 message ", " there were zero messages &quot)


Paul

 

[ChrisTheAncient]

Thanks Paul.

I tried that and the syntax wasn't rejected. (When I tried it, it wouldn't accept my syntax!)

What it has shown, though, is that I have made some daft assumptions in my underlying query that drives the report - and it will never work in the way I want! Back to the drawing board!

So, I shall have to design a 'nil return' report that will get called up as the records are cycled through and there are no messages to report! Serves me right for trying to be too clever I suppose.

Thanks for the help.

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[scriverb]

Why not use the DCount function directly to the table using the identifying controls in the report to select and count the mail messages. This way you really don't need any messages in the Detail Section to generate your counts.
Alphanumeric ID Example:
=DCount("[ID]", "MessageTable", "[ID] = '" & Me![MessageID] & "'&quot

The above example is assuming the [ID] field is alphanumeric. If numeric then the single quotes should be removed from the expression string and should look like this:
Numeric ID Example:
=DCount("[ID]", "MessageTable", "[ID] = " & Me![MessageID])

I will leave the rest of the message to you. Also, if you underlying query was selecting records for this report based upon a date range and other criteria then they should be included in the expression.

Post back if you have any questions.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???