Conditional Expression

[Nona]

In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the
expression entered in the query) No problem with that. However, Access
returns an error if a value for the number of units approved has not been
entered, is null or zero. And it's impossible to get an average of all the
records.

I'm sure there must be a simple solution, but I haven't been able to come up
with it. I will appreciate your help!

 

[Beetle]

Use the Nz function to convert nulls. In your case it might look like;

[UnitsUsed]/Nz([UnitsApproved], 0)

or something similar

 

[Ken Sheridan]

Fraid not; that would involve division by zero, which is mathematically
impossible, so would still give an error. If a result of zero is required
for Null or zero units approved then:

IIF(Nz([UnitsApproved],0)>0,[UnitsUsed]/[UnitsApproved],0)

If a result of Null is required:

IIF(Nz([UnitsApproved],0)>0,[UnitsUsed]/[UnitsApproved],NULL)

Ken Sheridan
Stafford, England

Use the Nz function to convert nulls. In your case it might look like;

[UnitsUsed]/Nz([UnitsApproved], 0)

or something similar
--
_________

Sean Bailey

In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the
expression entered in the query) No problem with that. However, Access
returns an error if a value for the number of units approved has not been
entered, is null or zero. And it's impossible to get an average of all the
records.

I'm sure there must be a simple solution, but I haven't been able to come up
with it. I will appreciate your help!

 

[Nona]

I get the error message even if the number of approved units is zero. If I
enter "1" as the value, then I no longer get the error message.

I don't particularly want to have "1" as a default value for new auths since
it would skew the numbers (but not by much) so that's one possible solution.




--
Nona

Use the Nz function to convert nulls. In your case it might look like;

[UnitsUsed]/Nz([UnitsApproved], 0)

or something similar
--
_________

Sean Bailey

In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the
expression entered in the query) No problem with that. However, Access
returns an error if a value for the number of units approved has not been
entered, is null or zero. And it's impossible to get an average of all the
records.

I'm sure there must be a simple solution, but I haven't been able to come up
with it. I will appreciate your help!

 

[Ken Sheridan]

Nona:

See my response above.

Ken Sheridan
Stafford, England

I get the error message even if the number of approved units is zero. If I
enter "1" as the value, then I no longer get the error message.

I don't particularly want to have "1" as a default value for new auths since
it would skew the numbers (but not by much) so that's one possible solution.




--
Nona

Use the Nz function to convert nulls. In your case it might look like;

[UnitsUsed]/Nz([UnitsApproved], 0)

or something similar
--
_________

Sean Bailey

In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the
expression entered in the query) No problem with that. However, Access
returns an error if a value for the number of units approved has not been
entered, is null or zero. And it's impossible to get an average of all the
records.

I'm sure there must be a simple solution, but I haven't been able to come up
with it. I will appreciate your help!

 

[Nona]

Perfect!

Thanks Very Very Much!
--
Nona

Nona:

See my response above.

Ken Sheridan
Stafford, England

I get the error message even if the number of approved units is zero. If I
enter "1" as the value, then I no longer get the error message.

I don't particularly want to have "1" as a default value for new auths since
it would skew the numbers (but not by much) so that's one possible solution.




--
Nona

Use the Nz function to convert nulls. In your case it might look like;

[UnitsUsed]/Nz([UnitsApproved], 0)

or something similar
--
_________

Sean Bailey


:

In a query, Access calculates a utilization rate by dividing the number of
units used by the number of units approved. (Named "burnrate" in the
expression entered in the query) No problem with that. However, Access
returns an error if a value for the number of units approved has not been
entered, is null or zero. And it's impossible to get an average of all the
records.

I'm sure there must be a simple solution, but I haven't been able to come up
with it. I will appreciate your help!