Format Problem

G

Guest

How do you format a phone field in a data access page to display a phone
number like (123) 456-7890 instead of 1234567890?

My underlying table and all my queries, forms and reports show the phone
numbers correctly. But, it doesn't display correctly in the data access page.
I've tried to change the (element) properties of the phone field to (000)
000-0000 and even tried making the properties the same as the original phone
field in the underlying table (i.e. I used the input mask with all the
exclamation points, semi-colons, nines etc.) All without success.

Thanks,
 
G

Guest

I entered Format(CellPhoneNumber,"(###) ###-####") in the Format field (Data
tab) of the Element Poperties of the CellPhoneNumber text field. I also tried
(###) ###-#### Neither way worked. Any other ideas?

Thanks,
 
G

Guest

I may have misunderstood where to place the Format code. Here's the SELECT
QUERY that my Data Access Page is based on:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position, Phones.CellPhoneNumber
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber = Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;

Where should I place the code?

Thanks!
 
T

Tony Toews

Training Spec. said:
How do you format a phone field in a data access page to display a phone
number like (123) 456-7890 instead of 1234567890?
Click to expand...

This assumes that you will only ever have phone numbers in North
America.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
F

fredg

I may have misunderstood where to place the Format code. Here's the SELECT
QUERY that my Data Access Page is based on:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position, Phones.CellPhoneNumber
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber = Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;

Where should I place the code?

Thanks!
Click to expand...

Your Input Mask has not been set up to be saved with the data.
See Access Help.

On your existing data, if you do not want to Update the existing
values to include the formatting,

NOTE: I would use @ instead of 0 as place holder, i.e.
Format([Phone],"(@@@) @@@-@@@@")
If the area code has not been entered, you will get ( ) 123-4567
instead of (000) 123-4567.

Within the SQL:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position,
Phones.Format([CellPhoneNumber],"(@@@)-@@@-@@@@")
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber =
Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;


Or .... leave the Query alone and format the control directly in the
report, using an unbound control:
=Format([CellPhoneNumber],"(@@@) @@@-@@@@")
 
G

Guest

Yes, in fact, only in Georgia.
--
John


Tony Toews said:
This assumes that you will only ever have phone numbers in North
America.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Click to expand...
 
G

Guest

Fred G.,

Thanks, your code, although it didn't work, was close enough that I figured
out how to make it work. Here's what worked:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position, Format([CellPhoneNumber],"(@@@)
@@@-@@@@") AS [Cell Phone Number]
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber = Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;

Now ALL the objects in my database (including the DAP) display the cell
phone field, correctly.

Many thanks to all who helped me.

Happy New Year to All!
--
John


fredg said:
I may have misunderstood where to place the Format code. Here's the SELECT
QUERY that my Data Access Page is based on:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position, Phones.CellPhoneNumber
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber = Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;

Where should I place the code?

Thanks!
Click to expand...

Your Input Mask has not been set up to be saved with the data.
See Access Help.

On your existing data, if you do not want to Update the existing
values to include the formatting,

NOTE: I would use @ instead of 0 as place holder, i.e.
Format([Phone],"(@@@) @@@-@@@@")
If the area code has not been entered, you will get ( ) 123-4567
instead of (000) 123-4567.

Within the SQL:

SELECT OrganizationalList.Department, Employees.LastName,
Employees.FirstName, Employees.Position,
Phones.Format([CellPhoneNumber],"(@@@)-@@@-@@@@")
FROM OrganizationalList INNER JOIN (Employees LEFT JOIN Phones ON
Employees.EmployeeNumber = Phones.EmployeeNumber) ON
OrganizationalList.OrganizationalNumber =
Employees.OrganizationalNumber
ORDER BY OrganizationalList.Department, Employees.LastName,
Employees.FirstName;


Or .... leave the Query alone and format the control directly in the
report, using an unbound control:
=Format([CellPhoneNumber],"(@@@) @@@-@@@@")
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting a Field 2
Format Phone Number in Listbox 1
Help with setting up phone number format 4
Formatting issue with imported telephone number! 2
Input mask & Format question 2
Formatting Phone Numbers in Excel 2007 Help 15
Format Phone Number Field VBA 2
How do I parse a phone number? 5

Top