• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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

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
howcheat
Asked:
howcheat
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
peter57rCommented:
"when I export it, the total column will not display as numbers"

What do you get then?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now