How do I get Access to display a UK telephone number?

#1 (**permalink**) 28-Jul-2006, 08:10 AM

How do I get Access to display a UK telephone number, without truncating it?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10166-how-do-i-get-access-display.html
IE. 01223456789 is displayed as 1223456789. No matter what I try it either
does this or throws up an error message. Any help would be grateful as I am
new to this side of computing.

*

#2 (**permalink**) 28-Jul-2006, 08:10 AM

easiest is to store it in a text field..
else use the format property .
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166

Pieter

"Dave Walling" Walling@discussions.microsoft.com> wrote in message
news:7E079602-1C2F-4067-9958-E99F9C584A71@microsoft.com...
> How do I get Access to display a UK telephone number, without truncating
> it?
> IE. 01223456789 is displayed as 1223456789. No matter what I try it either
> does this or throws up an error message. Any help would be grateful as I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
> am
> new to this side of computing.

#3 (**permalink**) 28-Jul-2006, 08:10 AM

I guess the field is formatted as a number which normally drops leading
zeros. I'd format it as Text then you can include spaces in your telephone
numbers as well

HTH - Sheila

"Dave Walling" wrote:

> How do I get Access to display a UK telephone number, without truncating it?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
> IE. 01223456789 is displayed as 1223456789. No matter what I try it either
> does this or throws up an error message. Any help would be grateful as I am
> new to this side of computing.

#4 (**permalink**) 28-Jul-2006, 08:10 AM

Dave,
First thing is that you need to ensure that the field you are saving the
data in is a text field, if Access thinks it is a number field it will lose
the leading zero, the next thing you can do is use an input mask to get a US
style telephone number format (0123) 456 7890 or create a custom one
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
depending on how you wish to display the data.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
Hope this helps
Peter

"Dave Walling" wrote:

> How do I get Access to display a UK telephone number, without truncating it?
> IE. 01223456789 is displayed as 1223456789. No matter what I try it either
> does this or throws up an error message. Any help would be grateful as I am
> new to this side of computing.

#5 (**permalink**) 28-Jul-2006, 08:10 AM

Per Dave Walling Walling@discussions.microsoft.com>:
>01223456789 is displayed as 1223456789. No matter what I try it either
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
>does this or throws up an error message. Any help would be grateful as I am
>new to this side of computing.

As others have suggested: make it a "Text" field.

Also, I'd avoid trying to be cute with formatting. Just store whatever the user
types in and do not try to format it. Reason: you can get bogged down with
extension formats, international dialing codes, and other unexpected additions
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
to the raw number.
--
PeteCresswell

#6 (**permalink**) 28-Jul-2006, 08:10 AM

As Pete says, echoing others, make it a text field. A phone number field is
only incidentally filled with numbers. No calculation is done on these
numbers, so trying to keep it a numeric field does nothing but keep you from
being able to format it the way you want to see it.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166

I also second storing exactly what the user types in unless there is a
compelling reason to format. If there is, then it will be worth the time it
takes to add a column somewhere (probably the Country column, which may
already exist), and write a function such as this:

Public Function FormatMyPhone(MyCountry as string, MyNumber as string) as
string

select case MyCountry
Case "CA"
...formatting code here
Case "USA"
...formatting code here
Case "Mexico"
...formatting code here
Case Else
FormatMyPhone = MyNumber
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
end select

exit function

end function

In the afterupdate event of the phone number textbox, you store the
formatted value using a call to your function. Then, store exactly that.

#7 (**permalink**) 28-Jul-2006, 08:11 AM

Agreed you need to store what the user types in, but you should give some
thought to validation of the data. For example there should not be any alpha
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
characters in the data, so you should check for that especially instances of
users mistakenly entering a capital i 'I' instead of a one '1'. It does
happen. But also bear in mind that some people enter numbers in the following
format +44 (207) 123 1234. Do you want to store the + sign? Do you want to
store the spaces? That is why I was recommending the use of an input mask, it
guides the user in how you want them to enter the data so that you end up
with some consistency, but behind the scene you control how the actual data
is stored in the database.

"Rick Wannall" wrote:

> As Pete says, echoing others, make it a text field. A phone number field is
> only incidentally filled with numbers. No calculation is done on these
> numbers, so trying to keep it a numeric field does nothing but keep you from
> being able to format it the way you want to see it.
>
> I also second storing exactly what the user types in unless there is a
> compelling reason to format. If there is, then it will be worth the time it
> takes to add a column somewhere (probably the Country column, which may
> already exist), and write a function such as this:
>
> Public Function FormatMyPhone(MyCountry as string, MyNumber as string) as
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
> string
>
> select case MyCountry
> Case "CA"
> ...formatting code here
> Case "USA"
> ...formatting code here
> Case "Mexico"
> ...formatting code here
> Case Else
> FormatMyPhone = MyNumber
> end select
>
> exit function
>
> end function
>
> In the afterupdate event of the phone number textbox, you store the
> formatted value using a call to your function. Then, store exactly that.
>
>
>

#8 (**permalink**) 28-Jul-2006, 08:11 AM

Per torch_music:
>Agreed you need to store what the user types in, but you should give some
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
>thought to validation of the data. For example there should not be any alpha
>characters in the data, so you should check for that especially instances of
>users mistakenly entering a capital i 'I' instead of a one '1'. It does

But why? Assuming the info is not tb used by autodialers - just by people....
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166

e.g. 011-44-51-296-1000 x43

and

610-297-0953, "George"

Could both be legitimate, useful numbers for a user.

Or even "Pete@Beerbelly.com"..... assuming that some users
might want to use the field as a "contact node" and not just a phone number.
--
PeteCresswell

#9 (**permalink**) 28-Jul-2006, 08:11 AM

torch_music wrote:
> you should give some
> thought to validation of the data.

Here's some thoughts on the subject (I'm going to break my own rule and
use only 'ANSI mode' wildcard characters ):

CREATE TABLE Test9 (

contact_detail_type VARCHAR(9) DEFAULT 'Business' NOT NULL,

CONSTRAINT TelephoneContactDetails__contact_detail_type__valu es
CHECK (contact_detail_type IN ('Business', 'Home', 'Other')),

national_number VARCHAR(20) NOT NULL,

CONSTRAINT telephone_contact_detail_national_number__pattern
CHECK
(
national_number NOT LIKE '%[!0-9 -]%'
AND national_number LIKE '%[0-9]%'
),

CONSTRAINT telephone_contact_detail_national_number__spaces
CHECK
(
national_number NOT LIKE ' %'
AND national_number NOT LIKE '% '
AND national_number NOT LIKE '% %'
),

CONSTRAINT telephone_contact_detail_national_number__hyphens
CHECK
(
national_number NOT LIKE '-%'
AND national_number NOT LIKE '%-'
AND national_number NOT LIKE '%--%'
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
),

extension_number VARCHAR(6),

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10166
CONSTRAINT telephone_contact_detail_extension_number__pattern
CHECK (extension_number NOT LIKE '%[!0-9]%'),

country_number VARCHAR(3) DEFAULT '44' NOT NULL,

CONSTRAINT telephone_contact_detail_country_number__pattern
CHECK (country_number NOT LIKE '%[!0-9]%'),

CONSTRAINT telephone_contact_detail_country_number__UK_only
CHECK (country_number = '44')
);

Jamie.

--