Iif Statement

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hi:

I have a query, and I need to calculate the varaince (pecentage) between two
fields:
([f2]-[f1])[f1];

my iif is: July%$:
IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July]))

I have solved the problem of div. by 0, when [july] is 0; but i get #error
when both [july$] and [july] are zero. Can you please tell me how to impose
the result o be 0 in this case?

Thank you,

Dan
 
Roger,

That doesn't address the problem. It isn't about nulls it is about dividing
by zero.

D,

If both [July$] and [July] are zero, then dividing by anything but 0 will
give you a reslult of 0, so I just decided to divide by 1. So, basically,
what you need to do is nest another IIF( ) statement inside of the first one,
so that if [July] is zero, then it checks to see if [July$] is zero and if
so, uses 1 as the denominator.

BTW, if either of these values could be Null, then I concur with Roger's
recommendation that you wrap the field names in the NZ( ) function, to force
NULL values to zero (0).

IIF([qrytestprice].recType = "FY09F", ([July$] - [July])/iif([July] = 0,
iif([July$] = 0, 1, [July$]), [July]))

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Roger Carlson said:
Use the Nz() function (NullToZero)

IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,nz([july$]),nz([July])))

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



D said:
Hi:

I have a query, and I need to calculate the varaince (pecentage) between
two
fields:
([f2]-[f1])[f1];

my iif is: July%$:
IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July]))

I have solved the problem of div. by 0, when [july] is 0; but i get #error
when both [july$] and [july] are zero. Can you please tell me how to
impose
the result o be 0 in this case?

Thank you,

Dan
Click to expand...
Click to expand...
 
Thank you and I appreciate it , Dale/Roger!

In the meantime I was testing and it looks that is working the following:

IIf([july$]=0,0,IIf(qrytestprice.rectype="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July])))

Thanks again,

Dan

Dale Fye said:
Roger,

That doesn't address the problem. It isn't about nulls it is about dividing
by zero.

D,

If both [July$] and [July] are zero, then dividing by anything but 0 will
give you a reslult of 0, so I just decided to divide by 1. So, basically,
what you need to do is nest another IIF( ) statement inside of the first one,
so that if [July] is zero, then it checks to see if [July$] is zero and if
so, uses 1 as the denominator.

BTW, if either of these values could be Null, then I concur with Roger's
recommendation that you wrap the field names in the NZ( ) function, to force
NULL values to zero (0).

IIF([qrytestprice].recType = "FY09F", ([July$] - [July])/iif([July] = 0,
iif([July$] = 0, 1, [July$]), [July]))

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Roger Carlson said:
Use the Nz() function (NullToZero)

IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,nz([july$]),nz([July])))

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



D said:
Hi:

I have a query, and I need to calculate the varaince (pecentage) between
two
fields:
([f2]-[f1])[f1];

my iif is: July%$:
IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July]))

I have solved the problem of div. by 0, when [july] is 0; but i get #error
when both [july$] and [july] are zero. Can you please tell me how to
impose
the result o be 0 in this case?

Thank you,

Dan
Click to expand...
Click to expand...
Click to expand...
 
You're right, of course. I don't know what I was thinking...if at all. :-)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dale Fye said:
Roger,

That doesn't address the problem. It isn't about nulls it is about
dividing
by zero.

D,

If both [July$] and [July] are zero, then dividing by anything but 0 will
give you a reslult of 0, so I just decided to divide by 1. So, basically,
what you need to do is nest another IIF( ) statement inside of the first
one,
so that if [July] is zero, then it checks to see if [July$] is zero and if
so, uses 1 as the denominator.

BTW, if either of these values could be Null, then I concur with Roger's
recommendation that you wrap the field names in the NZ( ) function, to
force
NULL values to zero (0).

IIF([qrytestprice].recType = "FY09F", ([July$] - [July])/iif([July] = 0,
iif([July$] = 0, 1, [July$]), [July]))

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Roger Carlson said:
Use the Nz() function (NullToZero)

IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,nz([july$]),nz([July])))

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



D said:
Hi:

I have a query, and I need to calculate the varaince (pecentage)
between
two
fields:
([f2]-[f1])[f1];

my iif is: July%$:
IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July]))

I have solved the problem of div. by 0, when [july] is 0; but i get
#error
when both [july$] and [july] are zero. Can you please tell me how to
impose
the result o be 0 in this case?

Thank you,

Dan
Click to expand...
Click to expand...
Click to expand...
 
Hey, it's early.

Have you had your coffee yet?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Roger Carlson said:
You're right, of course. I don't know what I was thinking...if at all. :-)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Dale Fye said:
Roger,

That doesn't address the problem. It isn't about nulls it is about
dividing
by zero.

D,

If both [July$] and [July] are zero, then dividing by anything but 0 will
give you a reslult of 0, so I just decided to divide by 1. So, basically,
what you need to do is nest another IIF( ) statement inside of the first
one,
so that if [July] is zero, then it checks to see if [July$] is zero and if
so, uses 1 as the denominator.

BTW, if either of these values could be Null, then I concur with Roger's
recommendation that you wrap the field names in the NZ( ) function, to
force
NULL values to zero (0).

IIF([qrytestprice].recType = "FY09F", ([July$] - [July])/iif([July] = 0,
iif([July$] = 0, 1, [July$]), [July]))

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Roger Carlson said:
Use the Nz() function (NullToZero)

IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,nz([july$]),nz([July])))

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi:

I have a query, and I need to calculate the varaince (pecentage)
between
two
fields:
([f2]-[f1])[f1];

my iif is: July%$:
IIf([qrytestprice].[rectype]="FY09F",([July$]-[July])/IIf([July]=0,[july$],[July]))

I have solved the problem of div. by 0, when [july] is 0; but i get
#error
when both [july$] and [july] are zero. Can you please tell me how to
impose
the result o be 0 in this case?

Thank you,

Dan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top