Solved

Numbers are exporting as text

Posted on 2016-11-14
5
35 Views
Last Modified: 2016-11-14
Experts,

I have a query with some fields like this:  ODDraw: IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],"")

When I export to excel, the column is text not currency.  I have also tried to apply a format such as #,##0.00;[Red](#,##0.00) thinking that the export would be as a currency and not text but this did not work either.  

How can I export as currency?
thank you in advance.
0
Comment
Question by:pdvsa
5 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41886121
Try..
format(IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],""),"Your Currency Format")
0
 

Author Comment

by:pdvsa
ID: 41886128
HI, yes I tried that as well.  I applied "Currency" and "#,##0.00;[Red](#,##0.00)"
==>still exports as text.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41886153
use the TextToColumns method against the column as a range, something like:

set fmtRange = sht.Range("C:C")
fmtRange.TextToColumns
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41886249
Your only option will be using Null for an empty field.

    ODDraw: IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],Null)

If that still converts to text, use a plain zero:

    ODDraw: IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],0)

or force a data type:

    ODDraw: CCur(IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],0))

/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 41886314
Gustav, that solved it!  I see what the issues was.  the "" text qualifier. Need it to be Null.  
ODDraw: IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],Null)
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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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