Solved

Query problem

Posted on 2014-01-05
6
258 Views
Last Modified: 2014-01-06
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
0
Comment
Question by:DatabaseDek
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39758073
Can't tell without seeing the actual data. The table in you sample is linked not local.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39758091
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
 

Author Comment

by:DatabaseDek
ID: 39758924
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39759346
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
 

Author Closing Comment

by:DatabaseDek
ID: 39759715
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 39760416
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question