Forum Home
Press F1
 
Thread ID: 10790 2001-08-12 21:35:00 Access 97 function Guest (0) Press F1
Post ID Timestamp Content User
16899 2001-08-12 21:35:00 I need help with a database I am creating.

I have three fields on a form P1, I1, and E1 which the user enters data into.

I have then created a function to make a calculation into a fourth unbound field.

This function is a calculation and will perform it anytime the form is updated.

The problem I have is that until all three fields have data in them I get a #error in the fourth field.

Is there any way to get rid of this?

Thanks 		Guest (0)
16900 2001-08-13 10:47:00 Hi Eric,
Well you have pretty much answered the question yourself, the #ERROR is more likely due to blank or NUll data in the fields used in the calculation. If you use the Nz() function (Null to Zero) any Null values are...well converted to Zeros therefore:
P1 + I1 + E1 becomes
nz(P1) + nz(I1) + nz(E1)

Let me know how you get on with this?

Regards 		Guest (0)
16901 2001-08-14 00:25:00 I have had a look at this and I cannot seem to get it to work.

I realize that there is going to be an error as there is nothing for the calculation to work out, what I am trying to avoid is the end user seeing #error.

I have included the formula:


Function LossOnDrying(P As Single, I As Single, E As Single) As Single
Dim Msg As String
Const mnErrDivByZero = 11, mnErrOverFlow = 6
Const mnErrBadCall = 5
On Error GoTo LossOnDrying_Err
LossOnDrying = ((I - E) / (I - P)) * 100
Exit Function

LossOnDrying_Err:
If Err.Number = mnErrDivByZero Or Err.Number = mnErrOverFlow Or Err = mnErrBadCall Then
LossOnDrying = 0
Else

Msg = 'Unanticipated error ' & Err.Number
Msg = Msg & ': ' & Err.Description
MsgBox Msg, vbExclamation
End If ' In all cases, Resume Next
' continues execution at
Resume Next ' the Exit Function statement.
End Function

I have tried to start with an if, then else statement to test the data and return a zero if the data sent to the function is null but that doesn't seem to work.

Thaks for any other assistance you may be able to provide.

Eric 		Guest (0)
16902 2001-08-14 04:25:00 Or you could use this formula for the calculated field:

=IIf([P1]is null,'',iif([e1] is null,'',iif([i1] is null, '',[e1]+[i1]+[p1])))

This will not display anything in the calc field until all fields contain an entry. 		Guest (0)
1