Automatically apending data in a table 2

[albyh]

I am trying to write a module that will automatically remove the first 9 characters of a fourteen digit record and just leave the last 5. Any help would be greatly appreciated.

Thanks

Alban

 

[scriverb]

I am a little confused at this point. I don't know what you have as the original table and what you have accomplished at this point. Please explain the table setup at this point in time. And what you want to be able to do in the future.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[albyh]

AA61EB30003798
AB13ID30005151
AB14SX30025736
AB37RD30007441

This is the format I originally had (Order Number)

I created a second field Order No and using the code you provided spilt the above into two field as shown below

Order Number Order No
AA61EB300 03798
AB13ID300 05151
AB14SX300 25736
AB37RD300 07441

That bit worked fine. The problem is I need to frequently import more data into this table. When I run the query you gave me the existing records in the table come out like this.

Order Number Order No
AA61EB300
AB13ID300
AB14SX300
AB37RD300

 

[scriverb]

The problem is the Update I provided is re-updating the existing records and there are only 9 characters of data. We need to have the Update query not re-update those records. This will require you to backup in your process to start over with the original table but the following SQL WHERE clause added to the end of your query should only update records that have not been split yet:

WHERE ((Len([Order Number])> 9));


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[albyh]

I couldnt get this working so I've just amended the query to create the new field from the last five digits and leave them intact in the old field. This way no data is being apended we are just creating a new field.

Thanks a lot

Alban

 

[scriverb]

Many ways to get the same results. Now you can sort by the 14 characters and use the extra 5 digits for whatever you need to.

Good luck with this project.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???