Solved Addition not working in a Query (1 Viewer)

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
Hi There,
I am trying to understand what I am doing wrong with the attached Query.
I extract and calculate a number of fields from two tables.
When I multiply one field by another it works perfectly. See field "CubeCost"
However when I try to add a number of fields the result is that the fields are concatenated together as per the two screen shots.
I have changed field names to remove the '$' sign in their names but still the fields in the Totl field are concatenated and not added.
What am I doing wrong?
 

Attachments

  • Total Qry.PNG
    Total Qry.PNG
    95.1 KB · Views: 237
  • TotalResult.PNG
    TotalResult.PNG
    135 KB · Views: 275

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
16,788
see your numbers in query is Left justified, most likely they are Text.

so use Val([field1] & "") + Val([field2] & "") on your query to add.

edit:
just saw your query, when you use Format(), the result of the function is String.
better add using the Base field.
 
Last edited:

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
see your numbers in query is Left justified, most likely they are Text.

so use Val([field1] & "") + Val([field2] & "") on your query to add.

edit:
just saw your query, when you use Format(), the result of the function is String.
better add using the Base field.
Thank you for your prompt reply but it doesn't seem to work as totl = 0
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    28.3 KB · Views: 215
  • Capture2.PNG
    Capture2.PNG
    110.4 KB · Views: 264

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
see your numbers in query is Left justified, most likely they are Text.

so use Val([field1] & "") + Val([field2] & "") on your query to add.

edit:
just saw your query, when you use Format(), the result of the function is String.
better add using the Base field.
Thanks I missed your edit so will try same!
 

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
Thanks I missed your edit so will try same!
Many thanks Arnelgp that did the trick! I probably wasted 2-3 hours trying to find out what I was doing wrong.

I really appreciate all the help you give to myself and others
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
16,788
i cannot edit my previous post.

so Remove the Format() from your query so that the result is Numeric.
on the Right Pane (Property), use the Format property.
if nothing is showing, just type Currency.
 

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
i cannot edit my previous post.

so Remove the Format() from your query so that the result is Numeric.
on the Right Pane (Property), use the Format property.
if nothing is showing, just type Currency.
Hi typing Currency in the format field solved another problem for me. I didn't see that option which is why I formatted the calculated fields. Once again a big thank you!!
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:40
Joined
Mar 14, 2017
Messages
6,678
It happened because you used Format() on Washout$, and then tried to do mathematics operation on Washout$.
Format turns things to text. And you can't perform math on text.
 

Users who are viewing this thread

Top Bottom