Addition of two fields

[Guest]

Good morning,

I am trying to sum two fields in a query. At present I have two fields,
Settlement_In and Settlement_Out and I am trying to sum these fields to
obtain on field called Settlement. Only one of the two fields will have a
value in it at any one time, and the Settlement_Out field will always be a
negative number.

At present the two fields are formatted as follows:
SETTLEMENT_IN: Format(NZ([SETTLEMENT_IN_EUR],0),"Fixed")
SETTLEMENT_OUT: Format(NZ([SETTLEMENT_OUT_EUR],0),"Fixed")

And the Settlement field is totalled as follows:
SETTLEMENT: [SETTLEMENT_IN]+[SETTLEMENT_OUT]

This works fine when the numbers being summed are positive, but does not
when one of the numbers is a negative (ie for a settlement out).

Eg
SETTLEMENT_IN SETTLEMENT_OUT SETTLEMENT
25000.00 0.00 25000.00
0.00 -1000.00 0.00-1000.00

Please could you assist with this?

 

[Allen Browne]

Several issues here.

Firstly, the Format() function returns a string.
Adding 2 strings is less than ideal.
Perhaps you can tell use the purpose of this? For example, could we use
Round() and typecast the result to Currency? The result would then be
numbers that can be added.

Something like this:

SETTLEMENT_IN: CCur((NZ(Round([SETTLEMENT_IN_EUR],2),0))
SETTLEMENT_OUT: CCur(NZ(Round([SETTLEMENT_OUT_EUR],2),0)
SETTLEMENT: [SETTLEMENT_IN]+[SETTLEMENT_OUT]

Depending on what you are doing in the query (sorting, grouping, ...), you
may need to use the full expression in the calculated field, i.e.:
SETTLEMENT: CCur((NZ(Round([SETTLEMENT_IN_EUR],2),0))
- CCur(NZ(Round([SETTLEMENT_OUT_EUR],2),0)

 

[Michel Walsh]

Hi,

SETTLEMENT: val([SETTLEMENT_IN])+val([SETTLEMENT_OUT])


should do, or, do not format in the first place. The result of a format is a
string, not a number, so + tries to concatenate two strings.


? "25000.00" + "0.00"
25000.000.00

? "0.00" + "-1000.00"
0.00-1000.00

? val("0.00") + val("-1000.00")
-1000


Use format only at the end, when it is time to present the result to the end
user, if possible, not before.

Hoping it may help,
Vanderghast, Access MVP