"Total" Value Blank.

[Foster]

Hope this makes sense. It doesn't really to me!!!

I have some fields in a report which are (among many other's) Quantity1, Price 1, Total 1, Quantity2, Price 2 and Total 2. Total 1 multiplies the quantity1 and price1 together.............and the same for total 2.
I then have a TotalAll which is Total 1 +Total 2.
Basically, if there are no values for Quantity2 and Price2, the total 2 will be blank and the TotalAll does not return a value.

I think this is a long winded way of asking a simply question so I apologise!!!


Many Thanks.

F.

 

[pbaldy]

It sounds like you have Null values (blank), which will carry into the totals. Try using the Nz() function to force a zero in place of the Null. Total1 might look like:

=Nz(Quantity1 * Price1, 0)

Look in help for more info on the Nz() function.

 

[Foster]

Paul, Thanks for that, it works a treat.

Just one other thing. The CanShrink option for Total 1 and Total 2 are set to yes. Is there a way of making the field blank if it is zero?
I have searched the forum's for "visible" but found no threads that are relative. I don't know VB so am lost.

Can you help?

Many Thanks.
F.

 

[pbaldy]

I think you could do it with VBA, but you don't really need to. Try something like this in place of the existing formula:

=IIf(Nz(Quantity1 * Price1, 0) = 0, "", Nz(Quantity1 * Price1, 0))

 

[Foster]

Sorry Paul, Me Again.

The Total 1 or Total 2 value doesn't appear when the value is 0 which is great.....but!!!!

TotalAll is Total1+Total2 but I just get a #ERROR if either Total1 or 2 has no value.

I am really really sorry to be a pain but I have been chucked in the deep end at work.

Thanks for all your help.

F.

 

[pbaldy]

The problem is that the empty string ("") is not numeric, so it screws up numeric formulas. You can test for an empty string ("") with the Len() function, so try:

=IIf(Len([Total1])=0,0,[Total1]) + IIf(Len([Total2])=0,0,[Total2])

 

[Foster]

Paul,
Thank you very, very much for your quick reply.
All working OK so I can now continue!!

Will go away and look at the NZ() and LEN() function. My books are at work and, to be honest, they are new to me!!

Once again, thanks.

F.