Formulas

[summer]

I have some formulas in a form. The formulas are computing square roots. If the result of the formula is a negative number, it gives an error (as a result of the square root calculation).

How do I change the formula to tell it to show a zero as the result in the case of an error or negative number?

 

[crosmill]

If FormulaResult < 1
FormulaResult = 0
End If

hth

 

[summer]

Which property should I put that code in? Or should I fit it into my formula? I'm not very good at this. The following is my formula if that helps:
=5.15*(Sqr([Text1135]))

 

[summer]

This is what I did:
=IIf(5.15*(Sqr([Text1135]))>1,5.15*(Sqr([Text1135])),0)

The formula seems OK, but I'm still getting an error. Because the formula is squaring a negative number, it is giving an error as the result should. I don't know if I could get around this. Should I be asking this question in an Excel forum?

 

[summer]

OK, OK...I'm working through this.

Here's what I need.

I need this to show a zero if my result is a negative number, not if the number is less than one.

Any suggestions???????

 

[crosmill]

I think you just got the formula the wrong way round

=IIf(5.15*(Sqr([Text1135]))>1,0,5.15*(Sqr([Text1135])))

Try that

Can you let me know exatcly what your doing with it, is it in access/excel ar you coding it, is it on a form.......?

 

[neileg]

Two problems:
1) You're testing after you have attempted to get the root, so you have already created the error.
2) Isn't the square root function SQRT


Although I have not tested this, I would have thought it should be

=IIf([Text1135]<0, 0, 5.15*(Sqrt([Text1135]))

 

[Mile-O]

SQR in Access; SQRT in Excel.

Probably best to create a function - that way you can use it to trap the error and return a 0 value. THe benefit there is that it will be easy to call it numerous times within your database.

 

[Mile-O]

Put this in a module:

Function SquareRoot(ByVal dblValue As Double) As Double

    On Error GoTo Err_SquareRoot
    
    SquareRoot = Sqr(dblValue)
    
Exit_SquareRoot:
    Exit Function
    
Err_SquareRoot:
    If Err.Number = 5 Then
        MsgBox "Unable to find square root of negative value.", vbExclamation, "Unable to perform action"
        SquareRoot = 0
    End If
    Resume Exit_SquareRoot

End Function

Then your controlsource can be:

= 5.15 * (SquareRoot([Text1135]))



It's just another way, saves you having to build moer complicated expressions if you plan on having a lot of them in your database.

 

[summer]

Thank you all for your help! The formula in my form is working now. In addition to using the formula given by Crosmill and changing my number format to General Number made it work.
(I'm not sure what the number thing was, but all of a sudden when I changed it, it worked!)

Thanks a bunch!!!
Summer