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

Numbers are exporting as text

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
pdvsa
Asked:
pdvsa
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Try..
format(IIf([Daily Draw/Repayment OD]>0,[Daily Draw/Repayment OD],""),"Your Currency Format")
0
 
pdvsaAuthor Commented:
HI, yes I tried that as well.  I applied "Currency" and "#,##0.00;[Red](#,##0.00)"
==>still exports as text.
0
 
Dale FyeCommented:
use the TextToColumns method against the column as a range, something like:

set fmtRange = sht.Range("C:C")
fmtRange.TextToColumns
0
 
Gustav BrockCIOCommented:
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
 
pdvsaAuthor Commented:
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

Technology Partners: 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!

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