Query Help Please

[Mark S via AccessMonster.com]

I need some assistance with this query that I am using. % retained: [sumofX]/
([sumofX]+[sumofrtc]). The problem with this query is that if only
transactions entered are X transactions I never show % retained until RTC
(roll to competitor) transactions are entered. I would like to be able to
show that if only X transactions are entered then % retained would be 100%
instead of blank. Same with RTC. If only RTC transactions are entered then %
retained would be 0% instead of just a blank.

Any help is greatly appreciated.

Thanks
Mark

 

[Allen Browne]

Use Nz() to specify zero for null, e.g.:
[sumofX] / ([sumofX]+Nz([sumofrtc],0))

 

[John Spencer]

I would try

NZ(SumOfX,0)/(Nz(SumofX,0)+ Nz(sumofRTC,0))

This will fail if BOTH SumOfX and SumOfRtc are zero or blank. In that case
exists, you will need to test the values with an IIF statement before attempting
the calculation.

IIF(SumOfX is Null and SumofRTC is Null,Null, <Insert working formula>)

 

[Mark S via AccessMonster.com]

Thank you so much. Obviously that worked. You're the best Allen.

Use Nz() to specify zero for null, e.g.:
[sumofX] / ([sumofX]+Nz([sumofrtc],0))

I need some assistance with this query that I am using. % retained:
[sumofX]/

[quoted text clipped - 5 lines]

%
retained would be 0% instead of just a blank.

 

[Mark S via AccessMonster.com]

Most excellent John...Thanks for going beyond with this

I would try

NZ(SumOfX,0)/(Nz(SumofX,0)+ Nz(sumofRTC,0))

This will fail if BOTH SumOfX and SumOfRtc are zero or blank. In that case
exists, you will need to test the values with an IIF statement before attempting
the calculation.

I need some assistance with this query that I am using. % retained: [sumofX]/
([sumofX]+[sumofrtc]). The problem with this query is that if only

[quoted text clipped - 8 lines]

Thanks
Mark