List Box Format of Phone Number

[JJ]

I have a list box that has several customer contact columns. One of them is a
phone number. I would like the format to appear in the list box the same as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999") "000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!

 

[Douglas J. Steele]

Create a query based on the table, and use the Format function in the query.

 

[JJ]

Ok, thought I had it. But what happens if the person doesn't want to fill in
the extension. The way I have it set up is that the numbers go to the right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put 9999 in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't figure it
out...

 

[Douglas J. Steele]

You could always write a function to format them as you'd like (rather than
using the built-in Format function), or you could store them the way you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, thought I had it. But what happens if the person doesn't want to fill
in
the extension. The way I have it set up is that the numbers go to the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put 9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't figure
it
out...

Create a query based on the table, and use the Format function in the
query.

 

[JJ]

So do you have any code that will help me? Basically, I just want the numbers
to fill in left to right... This will take care of the problem, as Extension
is optional. I don't know how to get this done...

You could always write a function to format them as you'd like (rather than
using the built-in Format function), or you could store them the way you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, thought I had it. But what happens if the person doesn't want to fill
in
the extension. The way I have it set up is that the numbers go to the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put 9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't figure
it
out...

Create a query based on the table, and use the Format function in the
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a list box that has several customer contact columns. One of them
is
a
phone number. I would like the format to appear in the list box the
same
as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999")
"000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!

 

[Douglas J. Steele]

I'd need to know exactly what is being stored in the text field in order to
show you the precise function you need, and unfortunately I'm leaving for a
week's vacation in a couple of hours so it's doubtful I'll be able to help
you in a timely fashion.

If your text field contains "12345678901234" and you want it to be (123)
456-7890 Ext 1234, you can use something like:

Function FormatPhone(PhoneNumber As String) As String

If Len(PhoneNumber) = 14 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Mid$(PhoneNumber, 7, 4) & " Ext " & _
Right$(PhoneNumber, 4)
ElseIf Len(PhoneNumber) = 10 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Right$(PhoneNumber, 4)
Else
FormatPhone = PhoneNumber
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

So do you have any code that will help me? Basically, I just want the
numbers
to fill in left to right... This will take care of the problem, as
Extension
is optional. I don't know how to get this done...

You could always write a function to format them as you'd like (rather
than
using the built-in Format function), or you could store them the way you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, thought I had it. But what happens if the person doesn't want to
fill
in
the extension. The way I have it set up is that the numbers go to the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put
9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't
figure
it
out...




:

Create a query based on the table, and use the Format function in the
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a list box that has several customer contact columns. One of
them
is
a
phone number. I would like the format to appear in the list box the
same
as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999")
"000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!

 

[JJ]

Remember, I'm talking about a list box... so try this.

Create a table and label the first column "Phone", then put the following
input mask on:
\(999") "000\-0000" Ext":9999;;_

Now enter the following three phone numbers into the table:
(888) 777-6666 Ext:5555
(222) 333-4444 Ext:77
(999) 555-8888 Ext:

Now create a form with a list box directed at this this table.

How can I keep the formating of those three numbers the same as in the
table. Right now, it strips the formating out.

I hope that makes sense, what I'm trying to get at...

I'd need to know exactly what is being stored in the text field in order to
show you the precise function you need, and unfortunately I'm leaving for a
week's vacation in a couple of hours so it's doubtful I'll be able to help
you in a timely fashion.

If your text field contains "12345678901234" and you want it to be (123)
456-7890 Ext 1234, you can use something like:

Function FormatPhone(PhoneNumber As String) As String

If Len(PhoneNumber) = 14 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Mid$(PhoneNumber, 7, 4) & " Ext " & _
Right$(PhoneNumber, 4)
ElseIf Len(PhoneNumber) = 10 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Right$(PhoneNumber, 4)
Else
FormatPhone = PhoneNumber
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

So do you have any code that will help me? Basically, I just want the
numbers
to fill in left to right... This will take care of the problem, as
Extension
is optional. I don't know how to get this done...

You could always write a function to format them as you'd like (rather
than
using the built-in Format function), or you could store them the way you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok, thought I had it. But what happens if the person doesn't want to
fill
in
the extension. The way I have it set up is that the numbers go to the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put
9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't
figure
it
out...




:

Create a query based on the table, and use the Format function in the
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a list box that has several customer contact columns. One of
them
is
a
phone number. I would like the format to appear in the list box the
same
as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999")
"000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!

 

[JJ]

Any thoughts on this?

Remember, I'm talking about a list box... so try this.

Create a table and label the first column "Phone", then put the following
input mask on:
\(999") "000\-0000" Ext":9999;;_

Now enter the following three phone numbers into the table:
(888) 777-6666 Ext:5555
(222) 333-4444 Ext:77
(999) 555-8888 Ext:

Now create a form with a list box directed at this this table.

How can I keep the formating of those three numbers the same as in the
table. Right now, it strips the formating out.

I hope that makes sense, what I'm trying to get at...

I'd need to know exactly what is being stored in the text field in order to
show you the precise function you need, and unfortunately I'm leaving for a
week's vacation in a couple of hours so it's doubtful I'll be able to help
you in a timely fashion.

If your text field contains "12345678901234" and you want it to be (123)
456-7890 Ext 1234, you can use something like:

Function FormatPhone(PhoneNumber As String) As String

If Len(PhoneNumber) = 14 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Mid$(PhoneNumber, 7, 4) & " Ext " & _
Right$(PhoneNumber, 4)
ElseIf Len(PhoneNumber) = 10 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Right$(PhoneNumber, 4)
Else
FormatPhone = PhoneNumber
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

So do you have any code that will help me? Basically, I just want the
numbers
to fill in left to right... This will take care of the problem, as
Extension
is optional. I don't know how to get this done...

:

You could always write a function to format them as you'd like (rather
than
using the built-in Format function), or you could store them the way you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok, thought I had it. But what happens if the person doesn't want to
fill
in
the extension. The way I have it set up is that the numbers go to the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually put
9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't
figure
it
out...




:

Create a query based on the table, and use the Format function in the
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a list box that has several customer contact columns. One of
them
is
a
phone number. I would like the format to appear in the list box the
same
as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999")
"000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!

 

[Douglas J. Steele]

Create the following query, and use it as the row source.

SELECT IIf(Len([Phone])=14,Format([Phone],"(000) 000-0000""
Ext:""0000"),IIf(Len([Phone])=12,Format([Phone],"(000) 000-0000""
Ext:""00"),IIf(Len([Phone])=10,Format([Phone],"(000) 000-0000""
Ext:"""),"***Error***"))) AS PhoneNumber
FROM MyTable;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Any thoughts on this?

Remember, I'm talking about a list box... so try this.

Create a table and label the first column "Phone", then put the following
input mask on:
\(999") "000\-0000" Ext":9999;;_

Now enter the following three phone numbers into the table:
(888) 777-6666 Ext:5555
(222) 333-4444 Ext:77
(999) 555-8888 Ext:

Now create a form with a list box directed at this this table.

How can I keep the formating of those three numbers the same as in the
table. Right now, it strips the formating out.

I hope that makes sense, what I'm trying to get at...

I'd need to know exactly what is being stored in the text field in
order to
show you the precise function you need, and unfortunately I'm leaving
for a
week's vacation in a couple of hours so it's doubtful I'll be able to
help
you in a timely fashion.

If your text field contains "12345678901234" and you want it to be
(123)
456-7890 Ext 1234, you can use something like:

Function FormatPhone(PhoneNumber As String) As String

If Len(PhoneNumber) = 14 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Mid$(PhoneNumber, 7, 4) & " Ext " & _
Right$(PhoneNumber, 4)
ElseIf Len(PhoneNumber) = 10 Then
FormatPhone = "(" & Left$(PhoneNumber, 3) & ") " & _
Mid$(PhoneNumber, 4, 3) & "-" & _
Right$(PhoneNumber, 4)
Else
FormatPhone = PhoneNumber
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


So do you have any code that will help me? Basically, I just want the
numbers
to fill in left to right... This will take care of the problem, as
Extension
is optional. I don't know how to get this done...

:

You could always write a function to format them as you'd like
(rather
than
using the built-in Format function), or you could store them the way
you
want them displayed.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok, thought I had it. But what happens if the person doesn't want
to
fill
in
the extension. The way I have it set up is that the numbers go to
the
right.
Here is my query:

Format(Customer_tbl.[Phone Number],"\(###) ###\-#### Ext:####;;_")

I want both of the following numbers to format the same way as
shown
below:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (943) 867-0534 Ext: [GOOD]

Right now the list box shows as follows:
1. (725) 456-3232 Ext:4322 [GOOD]
2. () 94-3867 Ext:0534 [BAD]

I tried to use 9999 at the end instead of ####, and it actually
put
9999
in
all the extensions and pushed 7 digits inbetween the (), like so:
1. (7254563) 232-4322 Ext:9999 [BAD]
2. (943) 867-0534 Ext:999 [GOOD]

I tried to use 000 at the beginning instesd of ### and it showed:
1. (725) 456-3232 Ext:4322 [GOOD]
2. (000) 094-3867 Ext:0534 [BAD]

Basically, I know this is probably just a small thing, but I can't
figure
it
out...




:

Create a query based on the table, and use the Format function in
the
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a list box that has several customer contact columns. One
of
them
is
a
phone number. I would like the format to appear in the list box
the
same
as
the input mask i have in place on the table.

I have the following input mask in my customer table: \(999")
"000\-0000"
Ext":9999;;_

Currently, the list box shows the number in constant numerals
ex.
8881234567. I want the list box to show (888) 123-4567.

Any help is appreciated. Thanks!