Remove last character in query

[josh64057]

I am trying to remove the last character using a query in access 2010. I only want to remove the last character if the lenght is 7 characters. If i use Left([CASCADE_ID],Len([CASCADE_ID])-1). I end up removing both "T" from the field that has 6 and 7 characters. I just want it to remove the "T" from the field with 7 charactes.

CT107T = do not change
CT106TT = need to change to CT106T

[ADezii]

UPDATE tblTest SET tblTest.CASCADE_ID = IIf(Len([CASCADE_ID])=7,Left$([CASCADE_ID],Len([CASCADE_ID])-1),[CASCADE_ID]);

[josh64057]

This work great. thank you!

[Rabbit]

Are they only at most 7 characters long? You can simplify by using left(field, 6)

[josh64057]

some of them are 10, but i need them to be 9. just like i needed the 7 to be 6.

[ADezii]

You never mentioned about the additional requirement for a String whose Length is 10. A slight change in the SQL Statement is required:

UPDATE tblTest SET tblTest.CASCADE_ID = IIf(Len([CASCADE_ID])=7, _
Left$([CASCADE_ID],Len([CASCADE_ID])-1),IIf(Len([CASCADE_ID])=10, _
Left$([CASCADE_ID],Len([CASCADE_ID])-1),[CASCADE_ID]));

[Mihail]

I think that this will work as well but is a little bit shortly than Rabit's (from where I had inspired):

IIF((Len(Cascade_ID)=7) OR (Len(Cascade_ID)=10),Left$([CASCADE_ID],Len([CASCADE_ID])-1),[CASCADE_ID])

More than, you can include as many OR clauses as you need without using a new IIF function for that.

[ADezii]

@Mihail:
Cleaner approach, and better than using Nested IIfs().

[NeoPa]

Josh:
some of them are 10, but i need them to be 9. just like i needed the 7 to be 6.

Why not specify the question clearly instead of just providing examples of exceptions only after the previous version of the question's solution(s) have been posted? That may save people wasting their efforts in this way.

[Mihail]

Sorry ADezii. I don't know why I write Rabit. Maybe because Rabit (like you) help me a lot.