Solved

Query problem

Posted on 2014-01-05
6
239 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 34

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

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 34

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now