By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Remove last character in query

P: 3
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
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,679
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

P: 3
This work great. thank you!
Dec 13 '11 #3

Rabbit
Expert Mod 10K+
P: 12,421
Are they only at most 7 characters long? You can simplify by using left(field, 6)
Dec 13 '11 #4

P: 3
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

ADezii
Expert 5K+
P: 8,679
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

100+
P: 759
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])
  2.  
More than, you can include as many OR clauses as you need without using a new IIF function for that.
Dec 14 '11 #7

ADezii
Expert 5K+
P: 8,679
@Mihail:
Cleaner approach, and better than using Nested IIfs().
Dec 14 '11 #8

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 14 '11 #9

100+
P: 759
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.