Query problem

See the attached image. I get the results from the query grid opposite.

You can see that without adding the "0+" to the grid I get a completely different result. The top example isn't even a number I don't think!

I opened a new database and imported the query and table thinking it may clear the problem. I even saved the sql code as a text file deleted the original and then pasted back the plain text. Did nothing.

Database sample attached.

Please take care I have scanned it and can see no nasty stuff.
db2.mdb
Untitled.jpg
Derek BrownMDAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Access is interpreting the calculated columns as strings.  I converted them to currency.


TTCost: CCur([TGICost])+CCur([TGCost])+CCur([TBCost])

You can use single or double if you prefer.

I always define all numeric values as currency to avoid floating point errors unless I need more than 4 decimal positions.

See "When Access Math doesn't Add up" at www.fmsinc.com.  It is just one of many great articles.
0
 
MacroShadowCommented:
Can't tell without seeing the actual data. The table in you sample is linked not local.
0
 
PatHartmanCommented:
The problem is that the fields you are trying to add are text rather than numeric.  So, the plus sign is concatenating them rather than summing them.  Change the data types in the table.  I can't look at your database because the table is linked and so I can't examine the table definition.

Also, when you create calculations, you need to be aware that if any of the values in the calculation is null, the calculation will return null.  To avoid the problem, use the Nz() function.

Nz(fld1,0) + Nz(fld2,0) + Nz(fld3,0)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Derek BrownMDAuthor Commented:
Sample enclosed.

Sorry about that.

I set all fields in the query and table to number but the problem persists.

I must be missing something!

The problem is with the last column on the query
db2.mdb
0
 
Derek BrownMDAuthor Commented:
Thanks Pat.

Been doing this for decades, never come across this before.

Thank you for your help.

Have you any idea why this would suddenly happen

Derek
0
 
PatHartmanCommented:
These are all calculated values and apparently Access has determined that there is some situation where at least one of them might end up as text and so it returns a string rather than a number.  I don't have time to download the db again and check but you could play with the calculations in a code module to see if you can figure it out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.