Truncate data

[DCV0204]

Hello, I have a table with a field that begins with '123 - ' with additional data to the right with different lengths. I need to remove the '123 - ' from the beginning of the field and leave the rest of the data.
Can I accomplish this using an update query?


Thanks

[JoeM]

Do all your entries start like this?
If so, you should be able to use the MID function in your Update statements to get whay you want, i.e.

=MID([FieldName],7,LEN([FieldName])-6)

If only some start that way, you can use:

Like "123 - *"

in your criteria to just apply it to those records.

[help_me_with_access]

if you want to target specific string data only, especially data that is consistent, regardless of it's position, use REPLACE(). that way you don't risk miscalculating byte positions with the arguments of functions like mid().

[JoeM]

I believe the question specified values that begin with "123 - ".
Won't REPLACE (without using anything else, i.e. criteria) replace it wherever it finds it in the string?
So if it occurs in the middle or at the end, it would incorrectly remove it, right?

[DCV0204]

Joe, all of my data begins with "3 numbers a space - space" (6 charac) I am wanting to remove that information and leave all the data to the right.
so if my field name is 'Spec' would I write the code as follows:
=MID ([Spec],7,LEN([Spec])-6)

What does the 7 represent?
Does the -6 represent the 6 charac I am deleting?
Thanks.

************************************************** ************************************************** ****

Do all your entries start like this?
If so, you should be able to use the MID function in your Update statements to get whay you want, i.e.

=MID([FieldName],7,LEN([FieldName])-6)

If only some start that way, you can use:

Like "123 - *"

in your criteria to just apply it to those records.

[JoeM]

This explanation of the MID function should answer your questions: http://www.techonthenet.com/access/f...string/mid.php

[DCV0204]

Problem solved.
Thanks.

This explanation of the MID function should answer your questions: http://www.techonthenet.com/access/f...string/mid.php