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

importing from excel formatting issue translation

I have a linked excel sheet.

I'm trying to import the data from that sheet to an access table.
The field in the excel sheet is called current_gp   and formatted as percentage 0.00  so the data reads
16.00%      for example

The field in the access table is formatted as text...trying to get the data imported correctly...but i need the percentage sign to be imported also..
If i need to change the formatting on the excel sheet i will.
But i cant change the field type in access..(which is "TEXT")

Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 5
  • 3
1 Solution
 
Gustav BrockCIOCommented:
  1. Change the format in Excel to a straight decimal number.
[list=2]Import as Currency or at least as Double[/list]
[list=3]Apply format "Percent" to the imported field.[/list]

Or use a query with an expression that uses Format to create your percent-formatted strings.
0
 
FordraidersAuthor Commented:
trying in the "FORMAT" property in the field in the access table

#.00 + %

or

#.00 & %

not working
0
 
Gustav BrockCIOCommented:
It should be just: Percent
And, perhaps, set the decimal count to 2 or 4.

Or: #.00%
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FordraidersAuthor Commented:
gustav, going to need the formatting in the append query itself...
The "PERCENT" works  but only if i type it in..

fordraiders
0
 
FordraidersAuthor Commented:
GUSTAV, ALMOST WORKING BUT COMNG THROUGH AS   0.16%


in append query:

Expr1: Format([CURRENT_GP]/100,"Percent")
0
 
Gustav BrockCIOCommented:
Well, don't divide by 100.
1
 
FordraidersAuthor Commented:
gustav:  this worked

CURRENT_GP_I: Format([CURRENT_GP],"Percent")

on the import .
0
 
FordraidersAuthor Commented:
Thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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