Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Dave Walling, Jul 28, 2006.

  1. Dave Walling

    Dave Walling
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    easiest is to store it in a text field..
    else use the format property .

    Pieter

    "Dave Walling" <Dave 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
    > am
    > new to this side of computing.
     
  4. Sheila D

    Sheila D
    Expand Collapse
    Guest

    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?
    > 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. torch_music

    torch_music
    Expand Collapse
    Guest

    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
    depending on how you wish to display the data.
    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.
     
  6. (PeteCresswell)

    (PeteCresswell)
    Expand Collapse
    Guest

    Per Dave Walling <Dave Walling@discussions.microsoft.com>:
    >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.


    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
    to the raw number.
    --
    PeteCresswell
     
  7. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    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
    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. torch_music

    torch_music
    Expand Collapse
    Guest

    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
    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
    > 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.
    >
    >
    >
     
  9. (PeteCresswell)

    (PeteCresswell)
    Expand Collapse
    Guest

    Per torch_music:
    >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
    >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....

    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
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    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 <g>):

    CREATE TABLE Test9 (

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

    CONSTRAINT TelephoneContactDetails__contact_detail_type__values
    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 '%--%'
    ),

    extension_number VARCHAR(6),

    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.

    --
     
  11. torch_music

    torch_music
    Expand Collapse
    Guest

    Because it leads to cleaner data in your database; the old axiom garbage in
    garbage out still applies.
    The storage of the data is separate to the presentation of the data. Do you
    need to store dashes and spaces and plus symbols in the database? I would say
    no, they are not intrinsic values of the data; they are presentation
    preferences and as such do not need to be stored.
    You will save a bit of space in the database, how much depends on how many
    numbers are stored. One or two bytes per number is not much when you only
    have twenty numbers stored, but when you have 20,000 or 200,000 those extra
    bytes can add up.
    it makes indexing more efficient by having more consistency in your data.
    Since it is a text field it will make searching easier, if you do not have
    some validation in there then you are dependant on the whim of the user when
    they entered the data. Are you going to search for 0207 123 1234 or
    0207-123-1234 or +44 (207) 1231234? You will need to know how the user
    entered the value otherwise you will not be able to retrieve it if you dont
    have validation and consistency.
    Dave specifically asked about UK telephone numbers, email addresses are a
    completely different issue. In that case it is much harder to build in
    validation because the range of acceptable characters in an email address is
    pretty much anything except a control character (yeah its a sweeping
    statement but without doing some serious research I dont think I am that far
    off the mark)

    "George" - legitimate; really? You'd have to convince me on that one.

    "x43" This is an interesting one and I think is a design decision that needs
    to be made. Are extentsion numbers going to be part of the 'main' telephone
    number or are you going to store them in a separate field?

    "(PeteCresswell)" wrote:

    > Per torch_music:
    > >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
    > >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....
    >
    > 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
    >
     
  12. (PeteCresswell)

    (PeteCresswell)
    Expand Collapse
    Guest

    Per torch_music:
    >"George" - legitimate; really? You'd have to convince me on that one.


    Not to us.... but to the user.
    --
    PeteCresswell
     
  13. quest

    quest
    Expand Collapse
    Guest

Share This Page