Formula problem, need some help

[vividor]

I have a report and I created a field name "available COMP TIME"(in hours)..
the formula should be something like this, (total hours earn + Ini comp) - (total hours req)

I was doing something like this "=(sum([hours earn])+([ini comp]))-sum([hours req]) , but
is not right.. can some one assist me on this formula. thanks

 

[Robert Dunstan]

Try =Sum(sum([hours earn]+[ini comp])+sum([hours req]))

 

[Robert Dunstan]

Ooops!!

Should have been

=Sum(sum([hours earn]+[ini comp])-sum([hours req]))

 

[vividor]

For some reason I am getting an #ERROR If I have do not have a record on (INI COMP), also
(HOURS EARN) is duplicating the value, for example, I enter (5 hours earned) on the report it shows
10 hours earned

 

[Robert Dunstan]

Hi there,

Firstly the reason for the #Error is (I think) that you are trying to calculate a result that includes a Null. If you have a zero in [INI COMP] then it should work i.e. prove that theory.

As for the formula, are you wanting to calculate each record in the report?

If so the try = [hours earn]+[ini comp]-[hours req]

If you're calculating totals then try = Sum([hours earn]+[ini comp])-Sum([hours req])

Post again if it doesn't work

Rob

 

[neileg]

If you don't want to convert your nulls to zeros in the data, you can do this in the formula using the Nz function.

 

[vividor]

Neileg, How does the Nz function works?
can you make an example for me?

Can I put that function on the report or queries?

 

[WayneRyan]

vividor,

Nz(YourField, 0)

Will return 0 if YourField is Null.

Wayne

 

[neileg]

Wayne is correct. You can use this in any formula, in queries or forms or reports. The second parameter, ,0 defaults to zero so you can omit this for simplicity.

 

[Dsquad]

Neileg, How does the Nz function works?
can you make an example for me?

Can I put that function on the report or queries?

In the control source?

=Nz(fieldname,0) ?????

 

[Rich]

Please stick with one post on your problem, it's also better to start a new thread
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=162027#post162027