During Import-force single data field from text to numeric - Help?!

G

Gilbert Noetzel

I need your help people.

I have created a macro to import bunch of database files from the
AS/400. Purpose is to move away from AS/400 to Microsoft Platform system.

One if the database file has a unique field. In the system (AS/400) it
is a numeric field but actually it is text when imported from AS/400 to
Access.

How do I force the import task to make that 1 field numeric?

Originally, when I manually imported the field, I changed the field from
text to numeric, it gave a warning saying that any data that is text
will be removed, and I agree with that and voila it is a nice clean
database in Access.

Now I want the Import to force the field to be numeric than text.

Do you have a solution?

Thanks in advance..

Gil
 
J

Jeff Boyce

Gil

A fairly standard approach to importing data into Access is to use
"temporary" tables.

Import your AS/400 into a table. Bear in mind that there's no guarantee
that the AS/400 data is well-normalized.

Create "parsing" queries that take data from the temporary table and
append/update your permanent Access tables (which, I'm sure, you've
normalized).

During these parsing queries, you can coerce one type of data to another
with functions like CStr(), CInt(), CDate(), etc.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top