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 assume you mean a 14 digit field.

UPDATE <YourTableName> SET <YourTableName>.[FieldName] = Mid$(<YourTableName>.[FieldName], 10, 5);

This will strip the first 10 characters off a field and leave just the last 5.


Bob Scriver

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

 

[albyh]

It is a 14 digit field.

What other code do I need to put with this to make it work?

Thanks

Alban

 

[scriverb]

Just update the table and field name in the SQL query code and run it. I take it that this is just a one time fix situation. If not then you can execute it in VBA code by the following:

DoCmd.OpenQuery &quot;<YourQueryName>&quot;

Bob Scriver

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

 

[albyh]

Again soory to be a pain but how do I exactly put this into a module to amke it work. This isnt a one time fix I want to run it everytime certain data is imported.

Thanks

Alban

 

[scriverb]

Save the query as we created it. Now you can call the query to be run in VBA code anytime that you perform your import process. If you do this with VBA code through a DoCmd.TransferDatabase type command then just follow that up with the following:
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryYourQueryName&quot;
DoCmd.SetWarnings True

You can put this code anywhere in your code just so it happens after the new imported file is finished.


Bob Scriver

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

 

[albyh]

How do I create the query?

Thanks

Alban

 

[scriverb]

The code that I gave you in my first post is the SQL code for a query. In design view click the queries tab. Now click new. Close the tables window. Select SQL from first button dropdown upper left. Paste the SQL code provided already in this window. Make changes to the areas that I indicated. Save the query and name it.

Now update the DoCmd.OpenQuery statement I provided with the name of your newly saved query.

It will run it every time that you click the button on the form.

Bob Scriver

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

 

[albyh]

Wahay it works.
Thanks a lot.
Alban

 

[albyh]

Sorry spoke to soon

When the query is run it changes the fields
This is the information as it is now.

VB7OPG30001549
TW86DL30001550
ST438130001551
SS46AX30001552

The last digits count upwards but when the query is run the data comes out like this

01549
01549
01549
01549
01549
01554
01554
etc

I cant understand why its doing this
Thanks
Alban

 

[scriverb]

Check your primary key. It probably has them sorted in an order that is creating this order.

Bob Scriver

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

 

[albyh]

There is two unique field in the table this field was the primary key but when i ran the query it only change the first 2000 and then threw up a key violation. I change dthe primary key to the other field that is unique and it then created this problem.

Alban

 

[scriverb]

Because we have stripped off the first 9 characters of the field the sort is lost forever. You see the field was sorted originally from left to right and the alpha characteders provided the major order for the display of the table. Now that they are gone the resulting values are lumped together. But, they cannot be the primary key alone as there are multiples of the 5 digits. Primary Keys have to be unique.

Just how would you like it sorted now. We could have just split the field and saved the first 9 in their own field and the 5 in their own field. Then the primary key could still be a combination of the two fields. Same sort but the 5 digits are readily available for what ever you need them for.

Let me know.

Bob Scriver

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

 

[albyh]

What code do I need to split the field in two

Thanks

Alban

 

[scriverb]

The problem here is that we have already stripped off the left 9 characters. And, left only the right 5. We can only do what I suggested if you have a backup of the original table with all 14 characters together.

Let me know where you stand on this capability.

Bob Scriver

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

 

[albyh]

Its alright I didnt use it on the original file a made a copy to test it.

 

[scriverb]

Using a copy now create a new field right next to the 14 character field. Make it a text type with a length of 5.

Now you can use the following SQL with table and name updates to split the field and have them both available for the sort and primary key.
UPDATE <YourTableName> SET <YourTableName>.[OrigFieldName] = Mid$(<YourTableName>.[OrigFieldName], 1, 9), <YourTableName>.[NewFieldName] = Mid$(<YourTableName>.[OrigFieldName], 10, 5);

Now set your Primary Key equal to the two field combination of the Original field and the new field. The sort will be like you wanted it and you will have the 5 characters seperated for use at originally needed.


Bob Scriver

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

 

[albyh]

I've got this working Ok but I cannot run this more then once. I need it to modify the new records everytime i import them. And is there anyway of getting it to change the data silently without the are you sure popping up.

Thanks

Alban

 

[scriverb]

By using the SetWarnings commands you can control the prompts that go along with running an Action query:
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryYourQueryName&quot;
DoCmd.SetWarnings True

As far as running more than once. Just put the above code right behind your code to perform the importing process. Then it will automatically parse the field for you. Are you importing with code or are you doing it by hand? If by hand I can help you to setup the code to import using VBA code.


Bob Scriver

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

 

[albyh]

Ive got all this working now except when the query is run the new field we have created becomes blank for all the records that were already in the table. If I get the new records to import into a seperate table is there any way of cutting all of the records from it and adding them to the existing table or is there any easier way.

Thanks

Alban