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

Update query

P: 6
Hello I have an excel spread sheet with a column called Acct_ID. Here is what I'm trying to do.

I have a column with account numbers in this form:

R12345
M23456
T12345

I'm trying to convert the first letter to a sequence of numbers then keep the remaining numbers. For example


R12345 to 10012345
M23456 to 20023456
T12345 to 30012345

I've created the following code to do so. But there's syntax errors and I'm unsure of what is wrong in my update query.

Expand|Select|Wrap|Line Numbers
  1. iif(left(ac,1)="R";"100"&right(ac,5);iif(left(ac,1 )="M";"200"&right(ac,5);iif(left(ac,1)="T";"300"&r ight(ac,5)))
Any ideas?
Jun 11 '12 #1
Share this Question
Share on Google+
3 Replies


P: 6
By the way the error I get is this:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks
Jun 11 '12 #2

100+
P: 124
Your Iif statement syntax is wrong. It should be:

Expand|Select|Wrap|Line Numbers
  1. IIf(Left([ac],1)="R","100" & Right([ac],5),IIf(Left([ac],1)="M","200" & Right([ac],5),IIf(Left([ac],1)="T","300" & Right([ac],5))))
Jun 11 '12 #3

P: 6
Thanks that worked! I appreciate your help

@BikeToWork
Jun 11 '12 #4

Post your reply

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