I want a 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") & IIf((Count([MessageID])>1),(" there were " & Count([MessageID]) & " messages "),(" there was " & Count([MessageID]) & " message "))

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

 

[Rich]

=Iif(IsNumeric([MyCountTextBox]),"On " & Format([Date],"dddd"", ""dd mmmm yyyy") & " there were " & Count([MessageID]) & " messages "),"There were no messages today")

On second thoughts
=Iif(IsNumeric([MyCountTextBox]),Iif([MyCountTextBox]>1,"On " & Format([Date],"dddd"", ""dd mmmm yyyy") & " there were " & Count([MessageID]) & " messages "),"There was one message today"),"There were no messages today")

 

[christheancient]

Hi Rich

Have tried that - but it throws up syntax problems about incorrect quotes.

Also noticed that - if it worked - this would give a slightly screwed up display if there was only one message of "There were 1 messages today".

I shall also go on looking, and see if I can use this IsNumeric thingy that I've not seen before. A new discovery for me.

Any more ideas out there?

Chris

 

[Rich]

Read the repost which was posted at the same time as your 2nd post, your date format isn't correct, perhaps someone else can be bothered to correct it for you

 

[christheancient]

Rich

I'm sorry, but your last comment has confused me.

Chris

 

[christheancient]

I have since found an underlying need to redsign the query that the report is based on.

So, thread CLOSED

Chris