Formatting phone numbers for list boxes

[Ron Weaver]

I'm beginning to believe you can't do it. My list box is tied to a query. The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing correctly.
Probably something really simple or impossible.

 

[Douglas J. Steele]

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

 

[Ron Weaver]

Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm beginning to believe you can't do it. My list box is tied to a query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Brian Bastl]

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian

Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm beginning to believe you can't do it. My list box is tied to a query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Ron Weaver]

Brian, I have tried making changes. I just don't know what I'm doing. Here is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian

Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[John Spencer]

SELECT Orders.StartDate, Orders.OrderID
, [FirstName] & " " & [LastName] AS Expr1,
Format(Customer.Phone,"(@@@) @@@-@@@@") as Telephone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;

Brian, I have tried making changes. I just don't know what I'm doing. Here
is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian

Hi Doug
Thanks for your response. I had tried previously, and I get this
message:
Circular reference caused by alias 'phone' in query definitions Select list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " &
[LastName] AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID =
Orders.CustomerID
WHERE (((Orders.StartDate) Between
[Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on
the phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output
are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Brian Bastl]

Ron,

your sql was fine other than format([Phone],"@@@) As Phone. It's the 'As
Phone' part that seems to be causing your circular reference. Just change
the last part to 'As CustPhone'.

Brian

Brian, I have tried making changes. I just don't know what I'm doing. Here is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian

Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " &

[LastName]
AS

Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on

the
phone

number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Ron Weaver]

John, thanks for responding.
I copied your code into my query. When I try to open my form I get the
following message: Enter Parameter Value: "Order Date. Phone". The results
in the query window are fine.

SELECT Orders.StartDate, Orders.OrderID
, [FirstName] & " " & [LastName] AS Expr1,
Format(Customer.Phone,"(@@@) @@@-@@@@") as Telephone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;

Brian, I have tried making changes. I just don't know what I'm doing. Here
is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this
message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " &
[LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID =
Orders.CustomerID
WHERE (((Orders.StartDate) Between
[Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on
the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a
query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output
are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Brian Bastl]

Forgot to mention that if your Listbox is bound, then you'll need to change
its control source to reflect the Alias name.

Brian

Ron,

your sql was fine other than format([Phone],"@@@) As Phone. It's the 'As
Phone' part that seems to be causing your circular reference. Just change
the last part to 'As CustPhone'.

Brian

Brian, I have tried making changes. I just don't know what I'm doing.

Here

is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a
query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers

output

are

 

[Ron Weaver]

Brian
I changed That to As CustPhone, and when I try to open my form Iget the
message: Enter Parameter Value "Order Date.Phone". I also copied John's code
into my query and got the same message. When I take the formatting code out
everything works fine, but the formatting.

Ron,

your sql was fine other than format([Phone],"@@@) As Phone. It's the 'As
Phone' part that seems to be causing your circular reference. Just change
the last part to 'As CustPhone'.

Brian

Brian, I have tried making changes. I just don't know what I'm doing. Here is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a
query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Ron Weaver]

That took care of it.
Thanks again.

Forgot to mention that if your Listbox is bound, then you'll need to change
its control source to reflect the Alias name.

Brian

Ron,

your sql was fine other than format([Phone],"@@@) As Phone. It's the 'As
Phone' part that seems to be causing your circular reference. Just change
the last part to 'As CustPhone'.

Brian

Brian, I have tried making changes. I just don't know what I'm doing.

Here

is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName] AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a
query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers

output

are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Brian Bastl]

Thanks for the update.

Brian

That took care of it.
Thanks again.

Forgot to mention that if your Listbox is bound, then you'll need to change
its control source to reflect the Alias name.

Brian

Ron,

your sql was fine other than format([Phone],"@@@) As Phone. It's the 'As
Phone' part that seems to be causing your circular reference. Just change
the last part to 'As CustPhone'.

Brian



Brian, I have tried making changes. I just don't know what I'm

doing.
Here

is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Ron,

The circular reference is caused by you aliasing the phone field with
the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this
message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " &
[LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID =
Orders.CustomerID
WHERE (((Orders.StartDate) Between
[Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on
the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is

tied to
a

query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output
are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.

 

[Ron Weaver]

John
Brian enlightened me in that I had to change my source code on the list box
to show the alias. All is well now.
Thanks so much for your help.

SELECT Orders.StartDate, Orders.OrderID
, [FirstName] & " " & [LastName] AS Expr1,
Format(Customer.Phone,"(@@@) @@@-@@@@") as Telephone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;

Brian, I have tried making changes. I just don't know what I'm doing. Here
is
the SQL before the formatting. If you can look at this and give me some
recommendations I sure would appreciate it.
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " & [LastName]
AS
Expr1, Customer.Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE (((Orders.StartDate) Between [Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

Ron,

The circular reference is caused by you aliasing the phone field with the
same name. Change the alias to something else.

HTH,
Brian


Hi Doug
Thanks for your response. I had tried previously, and I get this
message:
Circular reference caused by alias 'phone' in query definitions Select
list.
Please look at my SQL and see if this helps:
SELECT Orders.StartDate, Orders.OrderID, [FirstName] & " " &
[LastName]
AS
Expr1, Format([Phone],"(@@@)@@@-@@@@") AS Phone
FROM Customer INNER JOIN Orders ON Customer.CustomerID =
Orders.CustomerID
WHERE (((Orders.StartDate) Between
[Forms]![OrderDateForm]![txtStartDate]
And [Forms]![OrderDateForm]![txtEndDate]))
ORDER BY Orders.StartDate;
Thanks

:

Sounds as though you're using an InputMask. When you do, that just
affects
how the data appears, not how it's stored in the table.

In the query, use the Format function (not the Format property) on
the
phone
number field:

Format([PhoneNumber], "(@@@)@@@-@@@@")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm beginning to believe you can't do it. My list box is tied to a
query.
The
phone number is formated for (@@@)@@@-@@@@, but the numbers output
are
423888999. In the query datasheet view the formatting is showing
correctly.
Probably something really simple or impossible.