Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: formatting phone number data

    I have a phone number field in my database. It's a text field, no input mask in the table itself. There is an input mask in the form that's used to enter data.

    Some of the phone numbers were dumped into the database and are formatted like this:
    1234567890

    The rest of the phone numbers were formatted like this and are being entered like this:
    (123) 456-7890

    I want all of the numbers to be stored the second way. How do I convert the formatting of the numbers in the first example to the formatting of the second example (in the table) so that the formatting is uniform?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    How about this? Store them in 1234567890 format. You only need to report them in (123) 456-7890 format to screen or on paper. The extra 4 spaces per record in the table only waste space on your hard drive for no gain.

    Sam

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree the 'best' way is to store the number as a text value and then apply a formatr when required

    that may mean sanitising your existing data using an update query or series of queries to remove unwanted symbols.

    bear in mind that there is a differenece between how data is stored and how iit is displayed. a prime example is a date value.. it should be stored as the inherent datetime datatype (which is so many days since (IIRC) 31/12/1899 in Access but when displayed its usually formatted as dd/mm/yy or whatever the local variant of the date format is.

    if your application is to be used outside North America then storing numbers in a specific format will not work. if your application is going to store numbers for non North American style telephone numbers it will not work. I don't know how many countries format their numbers the same as the US/Canada, but you will find a plethora of formats in Europe.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by healdem View Post
    agree the 'best' way is to store the number as a text value and then apply a formatr when required

    that may mean sanitising your existing data using an update query or series of queries to remove unwanted symbols.

    bear in mind that there is a differenece between how data is stored and how iit is displayed. a prime example is a date value.. it should be stored as the inherent datetime datatype (which is so many days since (IIRC) 31/12/1899 in Access but when displayed its usually formatted as dd/mm/yy or whatever the local variant of the date format is.

    if your application is to be used outside North America then storing numbers in a specific format will not work. if your application is going to store numbers for non North American style telephone numbers it will not work. I don't know how many countries format their numbers the same as the US/Canada, but you will find a plethora of formats in Europe.
    It's only being used within North America, so that's not a problem.

    "Sanitizing the existing data" is exactly what I want to do. How do I do that?

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    To my mind, the best way is to make a routine. Add a temp field to the table, like TempPN, type text, length 10. Here's a sample routine (I use DAO, so my routine will reflect that):

    Code:
    Public Sub SanitizePN()
    
        Dim Cntr As Long, TempNo As String, Rst As DAO.Recordset
        
        Set Rst = CurrentDb.OpenRecordset("YourTableNameInQuotes", dbOpenTable)
        With Rst
            .MoveFirst
            Do While Not .EOF
                If .EOF Then GoTo ClosingTbl
                TempNo = ""
                For Cntr = 1 To Len(!TelphoneNumberFieldHereJustTheWayYouSeeIt)
                    If IsNumeric(Mid(!TelphoneNumberFieldHereJustTheWayYouSeeIt, Cntr, 1)) Then
                        TempNo = TempNo + Mid(!TelphoneNumberFieldHereJustTheWayYouSeeIt, Cntr, 1)
                    End If
                Next Cntr
                .Edit
                !TempPN = TempNo
                .Update
                .MoveNext
            Loop
    
    ClosingTbl:
    
        .Close
        
    End Sub
    For the real name and length of "TelphoneNumberFieldHereJustTheWayYouSeeIt," you have to use the name of the actual telephone number field, and the length allowed in the table as designed.

    After you complete the above to your satisfaction, delete the old phone number field and rename the field TempPN to the old name. In your data entry form, make sure you only allow for 10 numbers. You can set a format for that, and save the numbers without the format.

    If you have any numbers with extensions, you can either rely on chance (to only store the first 10 numbers) or you can delete the extension info manually before running the routine.

    Sam

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why go through all that palaver
    a simple update query replacing the unwanted characters will do the job just as well, OK you may need several queries but in principle

    update MyTable Set MyColumn = replace(replace(replace(MyColumn,"(",""),")","","-","")
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    guess so. but i think

    update MyTable Set MyColumn = replace(replace(replace(MyColumn,"(",""),")","","-","")
    s/b
    update MyTable Set MyColumn = replace(replace(replace(MyColumn,"(","",")","","-","")
    Sam

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Good point...
    Well made

    As ever its always a problem of typically aircode on forum
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by Sam Landy View Post
    guess so. but i think


    s/b

    Sam
    Thank You! (Everyone that posted)

    I don't have time to work on it right now, but I'll let everyone know if I have any problems using this function. It's exactly what I was looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •