Error on IIf statement in text box 1

[dbar10]

I have an unbound text box in a subform with the following IIf statement:

=IIf([Day]=1,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day1,IIf([Day]=2,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day2,IIf([Day]=3,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day3,IIf([Day]=4,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day4,IIf([Day]=5,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day5,IIf([Day]=6,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day6,IIf([Day]=7,Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form!Day7,0)))))))

The problem is when the record in Forms![Master Schedule]!SchHCWeekHrsSbfrm is Null because there is no record for this client. I get the proverbial #Error returned in my text box. I've got 4 more boxes with similar expressions and at least one of them will have a record to record. Can you help with a way around this where I can get a 0 returned when this occurs? Thanks for any help. You folks are wonderful.

 

[Frink]

Hallo,

Your best bet may well be to write a function to return the value you want.

- Frink

 

[TheAceMan1]

How are ya dbar10 . . .

Perhaps the following:

[blue]   IIf([Day]<8,Nz(Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form("Day" & [Day]),0),0)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

Thanks AceMan1. I tried your expression with the same results. (#Error) All these subforms are on my main form [Master Schedule]. The subform looking for the results is [SchDaySchedqry subform]. Each record is looking at the subforms below. If a client does not have one or more of the services the subform is blank (no records).

Forms![Master Schedule]!SchHCWeekHrsSbfrm
Forms![Master Schedule]!SchPCWeekHrsSbfrm
Forms![Master Schedule]!SchAPCWeekHrsSbfrm
Forms![Master Schedule]!SchRespWeekHrsSbfrm
Forms![Master Schedule]!SchLPNWeekHrsSbfrm

Can you help?

 

[TheAceMan1]

... and [Day] is on the mainform?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

No,[Day] is on [SchDaySchedqry subform]. Depending on the selection of [Day] will return results in [PC],[HC],[APC],[Respite] and [LPN] (these are unbound) in the same record. I get results fine when there is a record in the other subforms, just get error when there is no record. Thanks for you help.

 

[dbar10]

I have been banging on this until my fingers are soar.
AceMan provided a nice expression that streamlined my efforts but I still can't get around the issue of no records in one or more source subforms.

Let's start over:
I have an unbound text box [HC} in a subform [SchDaySchedqry subform]with the following IIf statement:

IIf([Day]<8,Nz(Forms![Master Schedule]!SchHCWeekHrsSbfrm.Form("Day" & [Day]),0),0)

As long as [SchHCWeekHrsSbfrm] has a record in it, I'm fine. But there will be times when there is no record and I get the #Error in my [HC] field. Instead of #Error I want it to populate a 0.

Does anyone know a way around this?

 

[dbar10]

I figured it out!!!

Because of AceMan1's expression above, it got me thinking. (Go figure) I went to the source of Forms![Master Schedule]!SchHCWeekHrsSbfrm and changed it to populate all records with 0 and matching dates with the companion subforms. Then I changed my unbound TextBox to Bound [HC]
Then I wrote a macro for AfterUpdate on [Day];
SetValue
Item: [Forms]![Master Schedule]![SchDaySchedqry subform].[Form]![HC]
And then used AceMan's expression
Expression:
=IIf([Day]<8,Nz([Forms]![Master Schedule]![SchHCWeekHrsSbfrm].[Form]("Day" & [Day]),0),0)

This now works great!! Thanks a lot.