Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

LEN of numeric string? 1

Status
Not open for further replies.

ericb1

Programmer
Oct 22, 2004
175
US
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!
 
Try this:

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

-LB
 
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!
 
The zero represents the number of decimals to display, and the "" removes the separating comma.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top