#Div/0! Error Message in Access

[Guest]

I have the following expression in a report in Access:
=Sum(NZ((IIf([Ind]="Actual-LIHTC",[1],0))+(IIf([Ind]="Actual-HTC",[1],0)))
-(IIf([Ind]="Original Proforma-LIHTC",[1],0)+(IIf([Ind]="Original
Proforma-HTC",[1],0))))
/NZ(IIf([Ind]="Original Proforma-LIHTC",[1],0))

I used Nz hoping to eliminate problems if either divisor or dividend is
zero. Can someone please tell me what I'm missing in this statement?
Thank you for your time.

 

[Douglas J. Steele]

The Nz isn't required in the denominator (NZ(IIf([Ind]="Original
Proforma-LIHTC",[1],0))): that's going to return 0 anytime Ind is Null (same
as whenever Ind isn't "Original Proforma-LIHTC"). You'll definitely get the
Divide by 0 error message in those cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"NoviceAccessUser-Melanie"

 

[Guest]

What would be a good way to get around this? If I remove the NZ around the
denominator, I still get the Divide by zero message on the report. The
denominator could be zero in most of the fields in my report.

The Nz isn't required in the denominator (NZ(IIf([Ind]="Original
Proforma-LIHTC",[1],0))): that's going to return 0 anytime Ind is Null (same
as whenever Ind isn't "Original Proforma-LIHTC"). You'll definitely get the
Divide by 0 error message in those cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"NoviceAccessUser-Melanie"

I have the following expression in a report in Access:
=Sum(NZ((IIf([Ind]="Actual-LIHTC",[1],0))+(IIf([Ind]="Actual-HTC",[1],0)))
-(IIf([Ind]="Original Proforma-LIHTC",[1],0)+(IIf([Ind]="Original
Proforma-HTC",[1],0))))
/NZ(IIf([Ind]="Original Proforma-LIHTC",[1],0))

I used Nz hoping to eliminate problems if either divisor or dividend is
zero. Can someone please tell me what I'm missing in this statement?
Thank you for your time.

 

[Pieter Wijnen]

iif(expr=0,1,expr)

HTH

Pieter

"NoviceAccessUser-Melanie"

What would be a good way to get around this? If I remove the NZ around the
denominator, I still get the Divide by zero message on the report. The
denominator could be zero in most of the fields in my report.

The Nz isn't required in the denominator (NZ(IIf([Ind]="Original
Proforma-LIHTC",[1],0))): that's going to return 0 anytime Ind is Null
(same
as whenever Ind isn't "Original Proforma-LIHTC"). You'll definitely get
the
Divide by 0 error message in those cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"NoviceAccessUser-Melanie"

I have the following expression in a report in Access:
=Sum(NZ((IIf([Ind]="Actual-LIHTC",[1],0))+(IIf([Ind]="Actual-HTC",[1],0)))
-(IIf([Ind]="Original Proforma-LIHTC",[1],0)+(IIf([Ind]="Original
Proforma-HTC",[1],0))))
/NZ(IIf([Ind]="Original Proforma-LIHTC",[1],0))

I used Nz hoping to eliminate problems if either divisor or dividend is
zero. Can someone please tell me what I'm missing in this statement?
Thank you for your time.