Formatting Phone Numbers in List Box 1

[OpalBiz]

I have a contacts database that stores phone numbers. The text box where these numbers are added and modified has the following input mask:

"(###) ###-####"

These phone numbers along with other information are queried to list boxes. Each number appears without the parentheses, space and hyphen just like they are stored in the table. For example, the list box displays 8005551212 rather than (800) 555-1212. How can the SQL statement be modified to format these numbers?

Below is an example of one SQL statement:

With Me.lstVendors
.RowSource = "SELECT ID, Contact, Phone FROM tblVendors"
.ColumnCount = 3
.ColumnWidths = "0;1.5 in;1.5 in;"
.ColumnHeads = True
.Requery
End With

Any help would be greatly appreciated.

 

[PHV]

Have you tried this ?
.RowSource = "SELECT ID, Contact, Format(Phone, '(###) ###-####') FROM tblVendors"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OpalBiz]

Thank you. That worked perfectly. I've been trying to use double quotes rather than single quotes.