Query Aggregate value won't subtract from Form Total

[lightray]

Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's

From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.

The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd

Any thoughts appreciated, I'm baffled

 

[gemma-the-husky]

3.52 - 8 isn't exactly -4.475, although it might be roundings.

I would check everything carefully as access doesn't normally get things like this wrong.

 

[Brianwarnock]

Gemma might be onto something here 3.52-8 = - 4.48 :
-4.48 +0.005 = -4.475 hmm , she's right you need to do some careful checking are you sure the missing 5 is really a 5.

brian

 

[lightray]

Strangely enough the 5 is a 5 Although I still haven't figured out the whys and wherefores of the Formula, switching it around has corrected the values. See enclosed image, both examples included.

Any insights welcome

 

[Brianwarnock]

I think its to do with the fact that you are working with text fields, but I cannot figure why that causes the problem, especially one way and not the other.

Brian

Of course [textstr1] +[textstr2] results in concatenation thus 3.52+5 results in 3.525, its been slow but its coming back to me now I seem to remember that by some idiotic logic Nz results in a text field use val(nz.... to ensure it remains numeric.

 

[lightray]

I think its to do with the fact that you are working with text fields, but I cannot figure why that causes the problem, especially one way and not the other.

Brian

The thing that's really got me is that DailyALInc is the smallest of all the values and I'm subtracting everything from it?

 

[Brianwarnock]

The thing that's really got me is that DailyALInc is the smallest of all the values and I'm subtracting everything from it?

Well not really the when you minus the minus ACDays you are adding it.

Did my last sentence

Of course [textstr1] +[textstr2] results in concatenation thus 3.52+5 results in 3.525, its been slow but its coming back to me now I seem to remember that by some idiotic logic Nz results in a text field use val(nz.... to ensure it remains numeric.

not help to clear things up? Still don't know how to do addition on text fields ,except by subtracting a minus , and why does subtraction work

Brian