Formatting

[mishbaker]

I'm not real good at Access yet, but I'm working on it. I need to import data from Excel and into Access. The data I'm importing comes to me from different places but each has the same information only formatted differently.

For instance. In the field the numbers are:

1234-56-789-0123
in another Excel file they are
123456789123
in another excel file they are
123456789

How can I get it to format them all into the last format type? In case your wondering the first 4 numbers in the first two formats are irrelevant. Inserted only because the people sending them to me are obviously trying to make my life difficult.

 

[Steve101]

Looking at your sample data, I think that you're meaning that the last four numbers in the first two formats are irrevalent; which leaves the first '123456789' numbers relevant . I'll assume that this is so, in which case there are two steps to resolving the three different formats:

(a) Remove any dash characters
(b) After this Use only the first 9 characters.

You can use a couple of standard Access functions to do this once the data is imported into Access:

I'll assume that the name of the field into which you import the data is called YourField

Then, the code for converting this into the third format might be:

Left(Replace(YourField,"-","&quot,9)

When called the above 'formula' (or expression) converts all dashes to 'empty strings'. the result is then truncated to 9 characters.

Hope this helps,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)