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
Solved

Numbers are exporting as text

Posted on 2016-11-14
5
38 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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…

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