Removing two letters from a table

[opostal]

I have a table where each field has two or three extra letters at the beginning of each entry. I simply want to do an update query that removes the unnecessary letters. Each field has the exact same two (although some fields have three) letters on each entry. For example all of field1 (Column1 if you were looking at it as a table) has CNSOUTH. Field two has OEMNORTH, and so on. I want to have the query go in and remove the first two letters (or three as the field has them). I am ok with it replacing them with spaces if necessary as I have built many a replacement queries that remove spaces. I just cannot seem to get this to work and not sure why.

Tried several variations of
Replace([Field1],XXXXX)

Thanks for any advice.

 

[vbaInet]

Have a look at the Left() and Len() functions.

 

[SOS]

Or the Mid Function -

Just set the update to be

for the ones with two letters at the beginning:
Mid([YourFieldName],3)

for the ones with three letters at the beginning:
Mid([YourFieldName],4)

 

[accessfever]

I think the Mid function from SOS should be like this:

Or the Mid Function -

Just set the update to be

for the ones with two letters at the beginning:
Mid([YourFieldName],3,len([YourfieldName]-2))

for the ones with three letters at the beginning:
Mid([YourFieldName],4,len([YourfieldName]-3) )

 

[SOS]

I think the Mid function from SOS should be like this:

Or the Mid Function -

Just set the update to be

for the ones with two letters at the beginning:
Mid([YourFieldName],3,len([YourfieldName]-2))

for the ones with three letters at the beginning:
Mid([YourFieldName],4,len([YourfieldName]-3) )

No, you don't need to set the length. Length is OPTIONAL and not necessary if you want the string from the starting point you've designated to the end. So, why make it harder than necessary?

 

[SOS]

accessfever - give it a try and see that I'm right

 

[vbaInet]

accessfever - give it a try and see that I'm right

Is he doubting your prowess? lol

 

[SOS]

Is he doubting your prowess? lol

Apparently

 

[vbaInet]

Come 'on accessfever, apologise. Hehe! Just kidding

 

[SOS]

Well, to be fair, many people do not know about the optional aspect of the Mid function. There are several functions with optionals like that.

 

[accessfever]

Sorry to have doubt about your MID function. I use my MID function like that in Excel a lot so I thought the MID function would be the same in Access.

 

[SOS]

It is nice that Access has some optional parameters. Excel should do it and it does within VBA but not within the spreadsheet function.

 

[opostal]

Awesome and it worked wonderfully thank you very much for the feedback. You guys are both way beyond my expertise so I have no room to doubt anyone.

 

[opostal]

As I stated before that worked beautifully, however I did not plan well enough. The problem is that it removed the letters needed, but then each day, more information is added to the database that will subsequently need the letters removed. If I run the query again, I will chop off two letters from previously trimmed items that I wish to keep while removing the two letters from the new data. I cannot manipulate the data coming in to prevent the letters from being there. Is there another solution to this problem? Thanks.

 

[SOS]

Yes, the solution is to import to a transitional table, do the chopping and then append to the regular table.

 

[opostal]

Yes, I think I knew that I was just ummm testing you. I should have thought of that myself before posting. Thank you again.

 

[SOS]

Glad I passed the test (good luck with it all )