Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have WorkPhone in a table define as follows:

    Fiels size: 10
    Input mask: !(999") "000-0000;;_

    Some records have incomplete numbers with only area code as follows:
    (___) ___-_716

    Some records have incomplete numbers missing the area code as follows:
    ( ) 863-6609

    I want to remove the 716 on these (___) ___-_716

    I want to add area code on these ( ) 863-6609

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    There is a superfluous space in your criteria, it should be "716" instead of " 716".

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    Actually there were 7 spaces before the 716, I changed as you suggested

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    Because the middle argument is blank, the input mask <code>!(999") "000-0000;;_</code> specifies that your phone numbers are actually stored without (, ), and -, e.g. what looks like (123) 456-7890 is stored as 1234567890. So try
    <code>
    ... SET strWorkPhone = "716" & strWorkPhone ...
    </code>
    (Click in the Input Mask property of a field or control and press F1 to learn more about the way fields with an input mask are stored)

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    I had already tried:

    <pre>UPDATE tbl_Dlicense SET tbl_Dlicense.strWorkPhone = "716" & [strWorkPhone]
    WHERE (((Len(Trim([strWorkPhone])))=7));
    </pre>


    This input
    ( ) 863-6609

    Returns this update
    716 8636

    Any other thoughts?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    I don't understand what is going on. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Isolate Incomplete Phone Numbers? (a2k (9.0.6926) SP-3 Jet 4.0

    Hans

    The following seems to have worked

    [pre
    UPDATE tbl_Dlicense SET tbl_Dlicense.strWorkPhone = "716" &
    Trim(Format([strworkphone],"@@@@@@@"))
    WHERE (((Len(Trim([strWorkPhone])))=7));
    [/pre]

    Let me know if there is a problem with this

    Thanks for your help

    John

Posting Permissions

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