Solved

Exported ms access 2007 to Excel 2007 query fails to display as numbers

Posted on 2012-04-12
3
243 Views
Last Modified: 2012-05-22
I have a left joined query that contains a simple calculated field Nz([CERTIFIED_US_AMT]-Nz([DEOB_AMT]))  with field named "Total". The two tables are humongous (almost 5 million records each) but still only add up to 1.8GB so it within Ms Access limits. BUT, when I export it, the total column will not display as numbers. I tried to use the "convert to Numbers" feature with no success. Never had this problem before. Query is attached.
0
Comment
Question by:howcheat
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37840629
how are Totals displayed?

try changing this

Nz([CERTIFIED_US_AMT]-Nz([DEOB_AMT]))

with

FormatNumber(Nz([CERTIFIED_US_AMT])-Nz([DEOB_AMT]),2)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37841697
"when I export it, the total column will not display as numbers"

What do you get then?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37843454
<"when I export it, the total column will not display as numbers"
What do you get then?>

Yeah, I'm curious as well, ...unless one of your fields is a string and you are inputting text (ex.: 45b, X41), then you will get #error

But even if both fields were text and you only input numeric values, your formula should only output numbers.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

791 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