use of not is null

[Guest]

I have the following statement and would like to do is calcualate where the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))

 

[Guest]

What error? I'm guessing a Divide By Zero would be one due to this part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it written.

 

[Guest]

just to add more detail this is what am trying to get into!

AvgPer% = avg(TotalNoProd/(TaskQ*TrackHrs))

Task TaskQ
A 15
B 30
C -
*Date01/01

Employee Task Trackhrs TotalnoProd Per%
1 A 7 93 88.57%

1 B 2.5 57 76.00%
1 c 2.5 --- ---
--------------------------------------------------------------------------
Total 12 150 82.29%

*Date 01/02

1 A 1

 

[Guest]

What do you think I should do, I have posted a some data as an example, I
dont want to calculate where the taskq is null.....

What error? I'm guessing a Divide By Zero would be one due to this part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it written.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following statement and would like to do is calcualate where the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))

 

[Douglas J. Steele]

=Avg(IIf(IsNull([taskq]),"0", nz([totalnoprod],0)/[taskq]*nz([trackhrs],0)))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What do you think I should do, I have posted a some data as an example, I
dont want to calculate where the taskq is null.....

What error? I'm guessing a Divide By Zero would be one due to this part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it written.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following statement and would like to do is calcualate where
the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))

 

[Guest]

Thanks for the reply, I tried it but it gave weird results e.g.,
Task A =15 Task B =30 task c =20

Task Hours TotalProd %
A 4.75 41 57.54%
B 3.08 50 54.11%
C .17 1 29.41%
------------------------------------------
604.17% ***

=Avg(IIf(IsNull([taskq]),"0", nz([totalnoprod],0)/[taskq]*nz([trackhrs],0)))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What do you think I should do, I have posted a some data as an example, I
dont want to calculate where the taskq is null.....

What error? I'm guessing a Divide By Zero would be one due to this part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it written.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following statement and would like to do is calcualate where
the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))

 

[Douglas J. Steele]

Are the 3 individual lines correct (sorry, too lazy to do the calculations!)

What's your formula that's calculating the 604.17%?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the reply, I tried it but it gave weird results e.g.,
Task A =15 Task B =30 task c =20

Task Hours TotalProd %
A 4.75 41 57.54%
B 3.08 50 54.11%
C .17 1 29.41%
------------------------------------------
604.17% ***

=Avg(IIf(IsNull([taskq]),"0",
nz([totalnoprod],0)/[taskq]*nz([trackhrs],0)))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What do you think I should do, I have posted a some data as an example,
I
dont want to calculate where the taskq is null.....



:

What error? I'm guessing a Divide By Zero would be one due to this
part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it
written.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following statement and would like to do is calcualate
where
the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))

 

[Guest]

Yes, I actually copied directly from the report.... those lines there
formulas is as follows [totalnoprod],0/([taskq]*[trackhrs])
Its correct and its also the same formula that I ahd at the total but the
problem comes when an employee forgot to add totlnoProd and things start
changing...

the formula that you wrote is the one calculating the 604.17%

Are the 3 individual lines correct (sorry, too lazy to do the calculations!)

What's your formula that's calculating the 604.17%?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the reply, I tried it but it gave weird results e.g.,
Task A =15 Task B =30 task c =20

Task Hours TotalProd %
A 4.75 41 57.54%
B 3.08 50 54.11%
C .17 1 29.41%
------------------------------------------
604.17% ***

=Avg(IIf(IsNull([taskq]),"0",
nz([totalnoprod],0)/[taskq]*nz([trackhrs],0)))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What do you think I should do, I have posted a some data as an example,
I
dont want to calculate where the taskq is null.....



:

What error? I'm guessing a Divide By Zero would be one due to this
part:

nz([totalnoprod],0)/(nz([taskq],0)

This would happen if [taskq] was either 0 or null as you have it
written.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following statement and would like to do is calcualate
where
the
taskq is not null but am getting an error... please help!


=Avg(IIf((nz(IIf([taskq],0))*nz([trackhrs],0))<>0,nz([totalnoprod],0)/(nz([taskq],0)*nz([trackhrs],0)),"0"))