Crystal 7.0 - Formatting return data 1

[cruford]

I am converting an Access 2000 database to SQL / VB. I am new to SQL and Crystal so forgive me if this is an easy question. All my searches here didn't turn up what I was looking for.

My SQL database stores phone numbers as 1234567890. I would like them displayed as (123)456-7890 on my crystal reports. Do I need to do this in my Stored Proc? If so how do I select a field and format it on the fly?

Example

********Stored Proc***************
CREATE PROCEDURE sp_Information
@CustID INT
AS
SELECT *
FROM
viewPatientInformation
WHERE
CustID = @CustID
GO

********Views***************

SELECT (whole lot of fields), PhoneNumber FROM ztblSomeTable


How do I get the PhoneNumber field displayed with formatting?

 

[lbass]

In CR, you could create a formula:

picture({table.phonestring},"(xxx) xxx-xxxx&quot

This assumes that {table.phone} is a string. If it is a number, change the formula to:

picture(totext({table.phonenumber},0,"&quot,"(xxx) xxx-xxxx&quot

I added in a space between the area code and the number, although you didn't have one--delete it if you really don't want it.

-LB

 

[lbass]

Oops, just realized you probably don't have the picture function in 7.0. Not sure what functions are available to you. If you can use the "left" and "mid" functions, you could do:

"(" + left({table.phonestring},3) + &quot " + mid({table.phonestring},4,3) +
"-" + mid({table.phonestring},7)

-LB

 

[vidru]

There's a .ufl that does this as well:

http://support.businessobjects.com/communityCS/FilesAndUpdates/uflphone.exe.asp

-dave

 

[cruford]

Our internet is pretty much locked down so I can get the file. I will look into the formulas, thanks for the syntax. I will post what I come up with. Thanks for the help.

 

[cruford]

Thanks lbass the suggestion below works great:

"(" + left({table.phonestring},3) + &quot " + mid({table.phonestring},4,3) +
"-" + mid({table.phonestring},7)

Here's a star