LEN of numeric string? 1

[ericb1]

Using CR10, I have a numeric field in my db that holds phone extensions. They're all 3 digit internal extensions, except for a few which are 7 digit outside numbers, stored in the 1234567 format.

I'm trying to display these as 123-4567 and the rest as just 123, with the following formula:

If LEN (ToText({MainUserTable.EXT})) > 3 Then
LEFT({MainUserTable.EXT},3) & "-" & RIGHT({MainUserTable.EXT},4)
Else
{MainUserTable.EXT}

However, I keep getting an error saying that the LEFT and RIGHT functions need a string, not a number. And if I convert all the functions to "ToText", then the final number is formatted incorrectly, with a decimal place on the end.

Any help is greatly appreciated, thanks!

 

[lbass]

Try this:

if len(totext({table.number},0,"")) > 3 then
picture(totext({table.number},0,""),"xxx-xxxx") else
totext({table.number},0,"")

-LB

 

[ericb1]

Wow, thanks! That's awesome. I didn't know about that picture command.

Just for my own info, what's the zero and "" in the (totext({table.number},0,"")
do?

Thanks again!

 

[lbass]

The zero represents the number of decimals to display, and the "" removes the separating comma.

-LB