Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query problem

Posted on 2014-01-05
6
Medium Priority
?
287 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 39

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 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 39

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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