Format in Text Field Changes

[EddieLampert]

Happy New Year.

I have a table that pulls information from an excel file to a database. In
the table, some of the text fields are no longer being displayed as percents,
as they are in the excel file. They show up in the table as decimals,
extending out about 10 decimal places (in excel it is a percent to one
decimal place). Any idea on how to keep the format the same as it is in excel?

Thank you.

 

[Jeff Boyce]

"... a table that pulls information..." -- This is a bit ambiguous.

Do you mean you have a link in an Access database that points to the Excel
file, or do you mean that you have a local table in Access that is "filled"
with data copied from the Excel file?

And if the latter, are you simply importing the data directly into the local
table in Access (probably what's causing the symptom you describe), or are
you using a query(ies) to "parse" the data from its "raw" (Excel) form into
your well-normalized Access table structure?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[EddieLampert]

It is a local table filled with data copied from the Excel file. Moreover, a
query is used to parse the data from its Excel form into the table.

Thanks again.

 

[Ken Sheridan]

The chances are that the actual values in the Excel file are those you see in
the Access table, but in the Excel worksheet they are formatted as a
percentage. This is the way a percentage would normally be stored, as a
fractional number, both in Excel and Access. Its seen as a percentage purely
by virtue of its formatting.

You can format the column in Access as Percent, but this would show it as a
percentage rounded to 2 decimal places. To show it rounded to one decimal
place set the format property to 0.0%. Bear in mind that any calculations
using the column will use the underlying precise value, not the rounded
value, e.g. a value of 0.456789 would show as 45.7%, but the value used in
calculations involving the column would be 0.456789.

Ken Sheridan
Stafford, England

 

[EddieLampert]

Correction. The data is simply imported directly into the local Access table.

 

[Jeff Boyce]

As Ken points out, formatting (for display purposes) is not necessarily the
same as the precise values stored.

If you are importing directly from Excel into Access, there's a good chance
that Access is "guessing" what data type to use.

If you link to the Excel data from within Access, you can build more
permanent, well-normalized Access tables that have the exact data types you
wish.

Then use queries to append to those permanent tables.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[a a r o n . k e m p f]

I'd start with using a real ETL tool if you're trying to do this
frequently

Access isn't reliable, it's not consistent

It's best to put conversions like this into the realm of SQL Server if
you want predictable results.

-Aaron