Trimming Data in a Field

[doyle]

Hi guys,

I'm in the process of importing a text file into Access. This is easy, obviously. :)

The only trouble is the data in the text file isn't in the format I would like.

The data should be a 9-digit number beginning with 21...

But the text file has it stored as a text field like this:

"QM000210000000"

The QM000 at the start is not required. I could find and replace this before importing, but I wanted to keep each process in Access, so to eliminate the steps needed.


At the moment, I import to a temporary table. The plan being, that I use some VBA to convert it, and append it to the main table.

Anyone got any pointers on how best to achieve this, and the commands that I will need to use?

My VBA knowledge isnt up to scratch, and I havent used it in a while.

Any advice would be great!

Regards

doyle

[CreganTur]

You can use the RIGHT() function to get just the last 9 characters of that first field. The syntax is RIGHT([FieldName],9) - that will pull the last 9 characters.

Do this as a part of the append query to move records from your temp table into your real table.

[doyle]

Great idea!!

Thats fanatastic, thank you!