Query Aggregate value won't subtract from Form Total (1 Viewer)

lightray

Registered User.
Local time
Tomorrow, 04:38
Joined
Sep 18, 2006
Messages
270
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:confused:
 

Attachments

  • scrdmp-6.gif
    scrdmp-6.gif
    12.6 KB · Views: 102

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Sep 12, 2006
Messages
15,614
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

Retired
Local time
Today, 15:38
Joined
Jun 2, 2003
Messages
12,701
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

Registered User.
Local time
Tomorrow, 04:38
Joined
Sep 18, 2006
Messages
270
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;)
 

Attachments

  • scrdmp7.gif
    scrdmp7.gif
    28.2 KB · Views: 96

Brianwarnock

Retired
Local time
Today, 15:38
Joined
Jun 2, 2003
Messages
12,701
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.
 
Last edited:

lightray

Registered User.
Local time
Tomorrow, 04:38
Joined
Sep 18, 2006
Messages
270
Brianwarnock said:
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?:confused:
 

Brianwarnock

Retired
Local time
Today, 15:38
Joined
Jun 2, 2003
Messages
12,701
lightray said:
The thing that's really got me is that DailyALInc is the smallest of all the values and I'm subtracting everything from it?:confused:

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:confused:

Brian
 

Users who are viewing this thread

Top Bottom