Difference between number formats

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I'm trying to standardize all of my excel spreadsheets
from various systems and in different formats before
importing them into Access.

What is the difference and effects, aside from cosmetic,
between the following number formats when importing into
Access? Does format affect calculations in Access as
well?

- number
- currency
- accounting
- general
 
Hi Sharon,

Access normally doesn't pay much attention to Excel numeric formats. If
you import Excel data to a new table, columns containing numbers are
normally imported as field type Number(Double), which matches Excel's
underlying number storage. Possibly - I haven't explored this - a column
formatted as Currency or Accounting will be imported to field type
Currency.

For best results, either define your Access table before importing
(field names must exactly match the Excel column headings) with the
field types you want; or else import the Excel data to a temporary table
and then use an append query to move it to your permanent table,
converting the data types as necessary.

Also, watch out for columns that contain a mix of numeric and
non-numeric values. These will screw up the import routine unless you
ensure that at least one of the first few cells in these columns
contains a value that cannot be interpreted as a number. Doing that
forces Access to import the column to a Text field.
 
Back
Top