Formatting phone numbers for list boxes

That took care of it.
Thanks again.

"Brian Bastl" wrote:

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

Brian


"Brian Bastl" wrote in message
...
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



"Ron Weaver" wrote in message
...
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

"Brian Bastl" wrote:

Ron,

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

HTH,
Brian


"Ron Weaver" wrote in message
...
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

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Ron Weaver" wrote in
message
...
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.

Thanks for the update.

Brian


"Ron Weaver" wrote in message
...
That took care of it.
Thanks again.

"Brian Bastl" wrote:

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

Brian


"Brian Bastl" wrote in message
...
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



"Ron Weaver" wrote in message
...
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

"Brian Bastl" wrote:

Ron,

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

HTH,
Brian


"Ron Weaver" wrote in
message
...
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

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Ron Weaver" wrote in
message
...
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
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.

"John Spencer" wrote:

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;


"Ron Weaver" wrote in message
...
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

"Brian Bastl" wrote:

Ron,

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

HTH,
Brian


"Ron Weaver" wrote in message
...
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

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Ron Weaver" wrote in message
...
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.