Formatting Number during Import or via Update Query

[Ketan]

Hey All,

I'm currently using Access 2007 (office provided) and I'm not a big fan of
their import feature, so I'm hoping you all can help me.

I have an Excel file that I'm importing from and I have a number of fields,
but 2 of them, which I use in reports very often. Both require 2-decimals,
one of which is currency.

Once the data is imported and in my table, I can modify the table to adjust
those 2 fields so that one is "Field Size: Double, Format: Standard" and the
other is "Field Size: Double, Format: Currency".

The problem is I can't set this when I import the data. It imports as "Field
Size: Double, Format: #,##0". This is causing me such pains because I have to
manually change it every time I import data. The problem with this format is
that when I run reports, it doesn't automatically show me 2 decimal places or
the dollar sign for the currency field.

I'm basically looking for either of two solutions.
1. Somehow adjust the import so that it imports with the correct number
format, although I haven't been able to find this.
2. Create an update query that I can just run to change the number format
for the table after I've imported the data.

Thanks in advance for any help with this!

 

[John W. Vinson]

I'm basically looking for either of two solutions.
1. Somehow adjust the import so that it imports with the correct number
format, although I haven't been able to find this.
2. Create an update query that I can just run to change the number format
for the table after I've imported the data

Or better, 3: have a permanent table in your database with the desired field
sizes, datatypes and formats, and import *into that table*. You may need to
run a Delete query to empty it each time if you don't want to accumulate data.