Deleting Part of A Field (Access 2003)

[WriterCat]

There is a customer field in my database that is padded with extra characters at the end - 00009 to be exact. Every customer number ends in those numbers. I'm interested in the numbers BEFORE those pad fields, which are the ones that make the customer unique. Unfortunately, the number of digits varies. A customer number could be 100009 or 12345600009.

How can I remove those trailing five digits?

The data comes to me with those pad figures, and I need to get rid of them so I have numbers that are consistenly formatted with the data I get from everywhere else (one affiliate has a different data processing system).

Thanks!

Nancy

[HansV]

Create a backup copy of the database before trying the following, to be on the safe side.

Let's say the field is named CustomerNumber.
Create a query based on the table.
Add the CustomerNumber field.
Select Query | Update Query.
Enter the following expression in the Update to line:

Left([CustomerNumber], Len([CustomerNumber])-5)

Select Query | Run and confirm.

[WriterCat]

Perfect, Hans, thank you.

I was trying to use Right and Len to subtract the characters - and that, of course, didn't work at all! Hadn't thought of using Left.

Thank you so much for you fast respose.

Nancy