Solved

Query problem

Posted on 2014-01-05
6
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 38

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 38

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 38

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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