Sorry to repost but..

[Guest]

I realised i didn't quite get the answer i was looking for.
I have an orders form taken from northwind. What i want is for the user to
be able to leave unitprice blank and if this is the case for a total of the
order not to be calculated. At the moment i have the following to calculated
the total for each line called LineTotal:
CCur(NZ(OrdersItems.UnitPrice,0)*[Quantity]). The following then calculates
the total for order: =Sum([LineTotal]).

If the user where to leave any one of the unitprice null, i would like
Linetotal and total to also be null or show something like n/a. Instead of an
error (which doesn't allow me to create a report) or zero (which gives a
false value to the total of the order). Thanks once again for any help.

TIA

Rico

 

[Guest]

I am hoping that this will help ... what about making a second cell to add
from and simply doing an if statement as it's control

IIF (isnull([Quantity]),0,[Quantity])

Method 2

if isnull ([Quantity]) then
[CellA] = 0
else if
[CellA] = [Quantity]
end if


This should give you the response you are looking for and also allow it to
calculate
as you specified.

 

[Guest]

Thanks for the suggestions Matt, unfortunatly i dont quite understand your
idea. If i use the IIF statement as a source it returns exactly what the
quantity field contains. Also where would i use the second statement you
entered?
The quantity field is not my problem as this will allways have a value its
the unitprice and subsequent calculations from it which are the problem.

I am hoping that this will help ... what about making a second cell to add
from and simply doing an if statement as it's control

IIF (isnull([Quantity]),0,[Quantity])

Method 2

if isnull ([Quantity]) then
[CellA] = 0
else if
[CellA] = [Quantity]
end if


This should give you the response you are looking for and also allow it to
calculate
as you specified.

I realised i didn't quite get the answer i was looking for.
I have an orders form taken from northwind. What i want is for the user to
be able to leave unitprice blank and if this is the case for a total of the
order not to be calculated. At the moment i have the following to calculated
the total for each line called LineTotal:
CCur(NZ(OrdersItems.UnitPrice,0)*[Quantity]). The following then calculates
the total for order: =Sum([LineTotal]).

If the user where to leave any one of the unitprice null, i would like
Linetotal and total to also be null or show something like n/a. Instead of an
error (which doesn't allow me to create a report) or zero (which gives a
false value to the total of the order). Thanks once again for any help.

TIA

Rico

 

[Wayne Morgan]

Sum will total the field LineTotal in all of the records in the recordset,
it won't total across a row. To total across a single record row, you'll
need to add each of the individual controls together.

=txtTextbox1 + txtTextbox2 + txtTextbox3...

If any one of these values is Null, the result will be Null. However, if
you've already made these textboxes non Null by using Nz, then zero will be
added and you'll get a result. Don't double count. In your example you have
a textbox that shows UnitPrice * Quantity and using Nz to return 0 if
UnitPrice is Null. Don't include this textbox in the addition above, instead
include the individual parts of it (UnitPrice and Quantity).

Example:
=txtTextbox1 + (txtUnitPrice * txtQuantity) + txtTextbox3

This way you would again have Null available to propagate through the
equation and give a Null result.