removing characters from a string

[pickslides]

Hi guys, wow 2nd post already, i'm a veteren!

I have a field that is of 'number' datatype. It's nine digits long and am keen to remove the first to 2 characters.

i.e 129132450 has to be 9132450 in a completely new feild. I'm thinking some sort of query might be able to to this. I know exactly how to do this in excel but my database is over 480000 records long. Excel gets angry at me when i try to import them

I'm only a new user so if anyone has a simple soln to what seems a complex problem pls help

 

[Mike375]

Create a new field in a query

NewFieldName:Right(([YourFieldName]),Len([YourFieldName])-2) That will work irrespective of the number of characters in the field

Search around on Right, Left, Mid and InStr and also Replace

Mid is kind of like Right but you limit how far across it goes. InStr allows to start from a character or a space.

 

[pickslides]

Cheers Mike, that worked a treat.

I've done that exact command in excel, I should really be more daring in the future and try things like that myself.

 

[raskew]

Hi -

I'm confused:

Mid is kind of like Right but you limit how far across it goes.

From the debug (Immediate) window:

x = 12345678

? mid(x, 3)
345678

Bob

 

[Mike375]

Mid([abc],3,5)

Like Right starting from the third character but only taking 5 characters instead of the rest of the string.

I probably worded the post poorly. Wasn't meaning for Mid etc to apply specifically to his post but just for general search on extracting characters.