Preventing #Error in calculated control

[Numpty]

I have some calculated controls on a form. They display figures based on information on a subform - No. of Courses, No, of Courses completed, No. of courses cancelled etc. This works fine as long as there are one or more courses within the subform.

If there are no courses at all within the subform the text box displays #Error - I have tried to use If IsNull within the form load but can't seem to get it to work.

Would that be the correct way? Otherwise how else could I prevent the form from showing #Error?

Cheers

 

[Mile-O]

Post what you have thus far...

 

[Numpty]

Private Sub Form_Load()
If IsNull(Text342) Then
Text342.Visible = False
End If


End Sub

Still displays #Error with that code

 

[Mile-O]

What's the ControlSource of the textbox, though?

 

[Numpty]

Sorry!

Text342 on the main form -

=[DelegateHistorySubform]!Text28 This points to a calculation done in the subform and displays the result


Text28 in the subform -

=DCount("[Status]","qryDelegateCourseHistory","[DelegateID] = " & [DelegateID] & " And [Status] = 'Completed'")

 

[Mile-O]

Have you tried the Nz() function?

=Nz([DelegateHistorySubform]!Text28,0)

 

[Numpty]

Copied that into the Control Source but it still displays #Error

 

[Numpty]

I'm now trying the following code and while still not working correctly rather than displaying #Error it just displays Error. It also now does the calculation if their are records existing.

=IIf(IsNull([DelegateHistorySubform]!Text28),0,[DelegateHistorySubform]!Text28)

Can anyone see what could be the problem with that code?

 

[neileg]

=IIf(IsNull([DelegateHistorySubform]!Text28),0,[DelegateHistorySubform]!Text28) is functionally the same as =Nz([DelegateHistorySubform]!Text28,0)

What is Text28 showing when you get the Error displayed?

Why do you need the same data in two places on the same form?

 

[Rich]

=Iif(IsNumeric([DelegateHistorySubform]!Text28, DelegateHistorySubform]!Text28,0))

 

[Numpty]

Cheers neileg

When I get the Error Text28 is displaying nothing - a blank field.

The reason I have this twice is that Text28 performs a calculation within the subform - I could not get it to perform the calculation in the main form - see above for the code. Text28 is not visible.
I then have a control on the main form to point to Text28 and that displays the result of the calculation.

 

[Numpty]

BINGO!

Slight modification on what you gave their Rich but now appears to be working.

=IIf(IsNumeric([DelegateHistorySubform]!Text28),[DelegateHistorySubform]!Text28,0)

Cheers for that Rich, thanks Mile-O and neileg for taking the time to look at it.