I have a calculated field in a query - Rate: [Fees]/[BilledHours]

If the fees are zero and the hours are zero I get an Error in that
field. How can I have it return a zero when it's dividing by zero?

Rosy wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the IIf() function.

Rate: IIf([BilledHours]=0,0,[Fees]/[BilledHours])



Syntax: IIf(expression, true result, false result)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfC9s4echKqOuFEgEQI/WACfW62fjIzyjlfbFBXtbdW3IcJ2N/oAoKcn
ohsszN3UxJdIR95jeUlePSCW
=IWNQ
-----END PGP SIGNATURE-----

On Apr 23, 1:56*pm, Rosy <krm... (AT) portlite (DOT) com> wrote:
Perfect! Thanks!

"MGFoster" <me (AT) privacy (DOT) com> wrote

expression even if [BilledHours] = 0.

If you do, try.

IIf([BilledHours]=0,0,[Fees]/IIf([BilledHours]=0,1,[BilledHours]))

VBA, yes. JET Sql, no.

Try these tests:

Sub temp()
Debug.Print IIf(1, 5, 5 / 0) 'error
End Sub

Sub temp2()
Debug.Print DBEngine(0)(0).OpenRecordset("SELECT IIf(1, 5, 5 / 0) FROM
MSysObjects")(0) 'r
End Sub

<rant>
That being said I am 100% opposed to IIf([BilledHours]=0,0,[Fees]/
[BilledHours])
If there are no hours then there is no rate.

My answer to:
How can I have it return a zero when it's dividing by zero?
is
take a time warp to another universe ... no, not even that. God cannot
make x / 0 = 0. Probably we should not try what defies God.

It is better both from a logical point of view and from modelling real
life to do:
Rate: [Fees]/[BilledHours]
WHERE BilledHours <> 0

and leave rate as null where BilledHours = 0.

Inevitably IIf([BilledHours]=0,0,[Fees]/[BilledHours]) will lead to
error because it is a contrivance, a dangerous bad contrivance.
Somwehere, sometime it will grab you and destroy you. Why. Because
it's WRONG!
Is this arrogant and pedantic? Sure. The reverse is why North American
manufacturing and business is failing. Rigor? We don't need no
stinking rigor! Just make some money today and get that bonus.
Eventually this will bring failure, has brought failure.
</rant>

On Apr 23, 5:45*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Rant = Yes
Correct = 100%