469,091 Members | 1,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Remove last character in query

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
Dec 13 '11 #1
9 5751
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.CASCADE_ID = IIf(Len([CASCADE_ID])=7,Left$([CASCADE_ID],Len([CASCADE_ID])-1),[CASCADE_ID]);
Dec 13 '11 #2
This work great. thank you!
Dec 13 '11 #3
12,516 Expert Mod 8TB
Are they only at most 7 characters long? You can simplify by using left(field, 6)
Dec 13 '11 #4
some of them are 10, but i need them to be 9. just like i needed the 7 to be 6.
Dec 13 '11 #5
8,800 Expert 8TB
You never mentioned about the additional requirement for a String whose Length is 10. A slight change in the SQL Statement is required:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.CASCADE_ID = IIf(Len([CASCADE_ID])=7, _
  2. Left$([CASCADE_ID],Len([CASCADE_ID])-1),IIf(Len([CASCADE_ID])=10, _
  3. Left$([CASCADE_ID],Len([CASCADE_ID])-1),[CASCADE_ID]));
Dec 13 '11 #6
759 512MB
I think that this will work as well but is a little bit shortly than Rabit's (from where I had inspired):

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 14 '11 #7
8,800 Expert 8TB
Cleaner approach, and better than using Nested IIfs().
Dec 14 '11 #8
32,159 Expert Mod 16PB
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.
Dec 14 '11 #9
759 512MB
Sorry ADezii. I don't know why I write Rabit. Maybe because Rabit (like you) help me a lot.
Dec 14 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

18 posts views Thread by Toto | last post: by
2 posts views Thread by Sharkie | last post: by
6 posts views Thread by Daniel Mark | last post: by
5 posts views Thread by dudeja.rajat | last post: by
reply views Thread by Fredrik Lundh | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.