Welcome to SPN

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

Sign Up Now!

Adding dash character to existing phone numbers

Discussion in 'Information Technology' started by Angie M., Jul 28, 2006.

  1. Angie M.

    Angie M.
    Expand Collapse
    Guest

    Hi, I'm using Access 03. I have a database with 2300 records in the main
    table. It contains multiple phone numbers for each individiual - direct,
    mobile, work, fax. I converted the data from another program and it came
    over without dashes, so the phone numbers look like this: 3039839838. I
    need for them to look like this: 303-983-9838. I've tried the Format
    @@@-@@@-@@@@ in both the table and form, but this causes lots of problems,
    the dashes "move" when a record is duplicated and are generally not
    consistent. The input mask won't work because I've got existing numbers I
    need to edit. I tried exporting into Excel to use the custom format, but I
    want to use that as a last resort because when I import back from Excel I
    loose all my other work on the table data types, lookups, etc.

    Is there a way to get those darn dashes into the phone numbers? I tried an
    update query also, and find and replace but Access doesn't seem to support
    wild cards in the find/replace. Any suggestions?? Thank you
     
  2. Loading...

    Similar Threads Forum Date
    Fortunate to be the son of the Great Guru Dashmesh Blogs Oct 21, 2015
    Dashrath Manjhi – The Man Who Moved a Mountain - The Mountain Man Inspirational Stories Sep 6, 2013
    India Its Dashmesh Kalgidhar Gatka Jatha (Hyderabad) Now In Action On Tv Breaking News Aug 30, 2011
    Dashing Docs & Some 42 km Talk Sports & Fitness Jan 14, 2011
    Sikh News Dasham Granth 300 years. Breaking News Nov 12, 2006

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try something like:

    UPDATE MyTable SET PhoneNumber = Format(PhoneNumber, "@@@-@@@-@@@@")
    WHERE Len(PhoneNumber) = 10

    (Test on a copy of the table first, just in case...!)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    news:63D5E71E-AE33-4624-BE2F-B2793CCE4A7F@microsoft.com...
    > Hi, I'm using Access 03. I have a database with 2300 records in the main
    > table. It contains multiple phone numbers for each individiual - direct,
    > mobile, work, fax. I converted the data from another program and it came
    > over without dashes, so the phone numbers look like this: 3039839838. I
    > need for them to look like this: 303-983-9838. I've tried the Format
    > @@@-@@@-@@@@ in both the table and form, but this causes lots of problems,
    > the dashes "move" when a record is duplicated and are generally not
    > consistent. The input mask won't work because I've got existing numbers I
    > need to edit. I tried exporting into Excel to use the custom format, but

    I
    > want to use that as a last resort because when I import back from Excel I
    > loose all my other work on the table data types, lookups, etc.
    >
    > Is there a way to get those darn dashes into the phone numbers? I tried

    an
    > update query also, and find and replace but Access doesn't seem to support
    > wild cards in the find/replace. Any suggestions?? Thank you
     
  4. Angie M.

    Angie M.
    Expand Collapse
    Guest

    Thanks but where do put this? In an update query??

    "Douglas J Steele" wrote:

    > Try something like:
    >
    > UPDATE MyTable SET PhoneNumber = Format(PhoneNumber, "@@@-@@@-@@@@")
    > WHERE Len(PhoneNumber) = 10
    >
    > (Test on a copy of the table first, just in case...!)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    > news:63D5E71E-AE33-4624-BE2F-B2793CCE4A7F@microsoft.com...
    > > Hi, I'm using Access 03. I have a database with 2300 records in the main
    > > table. It contains multiple phone numbers for each individiual - direct,
    > > mobile, work, fax. I converted the data from another program and it came
    > > over without dashes, so the phone numbers look like this: 3039839838. I
    > > need for them to look like this: 303-983-9838. I've tried the Format
    > > @@@-@@@-@@@@ in both the table and form, but this causes lots of problems,
    > > the dashes "move" when a record is duplicated and are generally not
    > > consistent. The input mask won't work because I've got existing numbers I
    > > need to edit. I tried exporting into Excel to use the custom format, but

    > I
    > > want to use that as a last resort because when I import back from Excel I
    > > loose all my other work on the table data types, lookups, etc.
    > >
    > > Is there a way to get those darn dashes into the phone numbers? I tried

    > an
    > > update query also, and find and replace but Access doesn't seem to support
    > > wild cards in the find/replace. Any suggestions?? Thank you

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Yes. I gave you the SQL for an Update query.

    Create a new query, but don't select any tables. Select SQL View from the
    View menu. Type what I have there (replacing MyTable with the appropriate
    table name, and PhoneNumber with the appropriate field name)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    news:85F33669-1981-48B1-88AB-32DBD22ABD14@microsoft.com...
    > Thanks but where do put this? In an update query??
    >
    > "Douglas J Steele" wrote:
    >
    > > Try something like:
    > >
    > > UPDATE MyTable SET PhoneNumber = Format(PhoneNumber, "@@@-@@@-@@@@")
    > > WHERE Len(PhoneNumber) = 10
    > >
    > > (Test on a copy of the table first, just in case...!)
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    > > news:63D5E71E-AE33-4624-BE2F-B2793CCE4A7F@microsoft.com...
    > > > Hi, I'm using Access 03. I have a database with 2300 records in the

    main
    > > > table. It contains multiple phone numbers for each individiual -

    direct,
    > > > mobile, work, fax. I converted the data from another program and it

    came
    > > > over without dashes, so the phone numbers look like this: 3039839838.

    I
    > > > need for them to look like this: 303-983-9838. I've tried the Format
    > > > @@@-@@@-@@@@ in both the table and form, but this causes lots of

    problems,
    > > > the dashes "move" when a record is duplicated and are generally not
    > > > consistent. The input mask won't work because I've got existing

    numbers I
    > > > need to edit. I tried exporting into Excel to use the custom format,

    but
    > > I
    > > > want to use that as a last resort because when I import back from

    Excel I
    > > > loose all my other work on the table data types, lookups, etc.
    > > >
    > > > Is there a way to get those darn dashes into the phone numbers? I

    tried
    > > an
    > > > update query also, and find and replace but Access doesn't seem to

    support
    > > > wild cards in the find/replace. Any suggestions?? Thank you

    > >
    > >
    > >
     

Share This Page