Trying to import 12 digit number into Access

[Guest]

I'm having problems importing a 12 digit number into Access. I've tried to
import from various file formats, Excel, Monarch, Dbf... When data is
imported into Access, the 12 digit numeric field is changed to a scientific
value. (i.e. 111111111111, becomes 1.0000+E11) I would like to be able to
do this import daily without having to go to design view of my table and
setting the format.

 

[Jeff Boyce]

Linda

First, is the "number" actually a number? That is, are you going to do math
with it, or is it a string of characters which happen to be digits? Import
as text if you aren't doing math on it.

Second, when you import, are you importing to a pre-existing Access table?
If so, remember that the data type of a field, and the display format of the
field don't necessarily coincide (e.g., you can display a date/time value of
"8/12/2003 10:01:01PM" as "803" -- this doesn't change the value, just what
gets displayed).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff

This is actually a number and the table is replaced with each new import.
I'm not appending new data to existing data. I'm not exactly doing math on
it, however, I will be searching a range (between 111111111111 and
123456789999). I was hoping to find a way to do the import without having to
go to design view each time and formatting.

 

[Guest]

Jeff

Thanks for the reply. The new data is imported daily with the new table
overwriting the old table. I won't be appending new data to an existing
table. The 12 digit number is actually and number.

I won't actually be doing math on it, however, I will be searching ranges
(between 111111111111 and 1234567899999). I'm hoping to find a way to import
daily without having to go to design view each day and change the format for
this field.

 

[Jeff Boyce]

Linda

If you aren't adding/subtracting them, they aren't numbers. You can use a
query to search a range of text values, even if those text values are
"111111111111" and "123456789999".

Create a (permanent) table in Access that has the data types you need.

Create a query that deletes all records from that table.

Import your data to a "temp" table (just let Access do its thing during an
import).

Create a query that appends records from the temp table into the permanent
table, converting the data as needed.

That last step is how you make sure the data is in the data type/format you
need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CGuzman]

I'm having problems importing a 12 digit number into Access. I've tried to
import from various file formats, Excel, Monarch, Dbf... When data is
imported into Access, the 12 digit numeric field is changed to a scientific
value. (i.e. 111111111111, becomes 1.0000+E11) I would like to be able to
do this import daily without having to go to design view of my table and
setting the format.

Here's what I do. It's wonky, but it works. I change the format to custom 12 9s. Save. And, then change format to text. Import and you get your 12 character field.

 

[anandpgt]

Can you please explain what is Custom 12 9s and how to change ?