Solved

Numbers are exporting as text

Posted on 2016-11-14
5
41 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
[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
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 50

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

730 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