Solved

Numbers are exporting as text

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

Expert Comment

by:Pawan Kumar Khowal
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now