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

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

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

or something similar
--
_________

Sean Bailey


"Nona" wrote:

> 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

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

"Beetle" wrote:

> Use the Nz function to convert nulls. In your case it might look like;
>
> [UnitsUsed]/Nz([UnitsApproved], 0)
>
> or something similar
> --
> _________
>
> Sean Bailey
>
>
> "Nona" wrote:
>
> > 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!
> >
> >
> > --
> > Nonap

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


"Beetle" wrote:

> Use the Nz function to convert nulls. In your case it might look like;
>
> [UnitsUsed]/Nz([UnitsApproved], 0)
>
> or something similar
> --
> _________
>
> Sean Bailey
>
>
> "Nona" wrote:
>
> > 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

Nona:

See my response above.

Ken Sheridan
Stafford, England

"Nona" wrote:

> 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
>
>
> "Beetle" wrote:
>
> > Use the Nz function to convert nulls. In your case it might look like;
> >
> > [UnitsUsed]/Nz([UnitsApproved], 0)
> >
> > or something similar
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "Nona" wrote:
> >
> > > 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


"Ken Sheridan" wrote:

> Nona:
>
> See my response above.
>
> Ken Sheridan
> Stafford, England
>
> "Nona" wrote:
>
> > 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
> >
> >
> > "Beetle" wrote:
> >
> > > Use the Nz function to convert nulls. In your case it might look like;
> > >
> > > [UnitsUsed]/Nz([UnitsApproved], 0)
> > >
> > > or something similar
> > > --
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "Nona" wrote:
> > >
> > > > 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
>