Update query

[Mnelson]

Hello I have an excel spread sheet with a column called Acct_ID. Heres 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.

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?

[Mnelson]

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

[orange]

This is an Access forum - is your question Excel or Access related?
Please provide more info if it Access/vba related.


Table layout, field names etc.
Show the sql for your update query.

[ssanfu]

There is a missing closing parenthesis and the semi-colons should be commas. The last FALSE clause is also missing. Here are two ways of writing the formula:

  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), "INVALID PREFIX- Not R, M or T")))

or

  IIf(Left(ac, 1) = "R", "100", IIf(Left(ac, 1) = "M", "200", IIf(Left(ac, 1) = "T", "300", "INVALID PRFIX- "))) & Right(ac, 5)