Insert Text 1

[0212]

I have a list of text formated number IDs. In some cases, I don't want to do anything and in other cases, I want to add a hyphen or dash after the first three characters. What formula should I use in Query Design Mode? Here is what I came up with so far:

Expr1: IIf([Claimnbr] Like "*[-]*",[Claimnbr],Replace([Claimnbr],"*","???"+"-"))

This expression works for ids that already have the hyphen. However, for the ids that have no hyphen, it returns the original id.

Examples of List:

9410123456 I want to convert to: 941-0123456
9700654321
941-0134567 I don't want to do anything and keep string
9410234567
970-0912345

Thanks for any help!

Jim.

 

[jrbarnett]

Expr1: IIf([Claimnbr] Like "*[-]*",[Claimnbr],Left([Claimnbr],3) & "-" & Mid([Claimnbr],4))

John

 

[0212]

Thanks, John! It worked like a charm!!

Jim.