Consulting

Results 1 to 3 of 3

Thread: Phone Numbers

  1. #1
    VBAX Mentor Marcster's Avatar
    Joined
    Jun 2005
    Posts
    434
    Location

    Phone Numbers

    Hi People,

    In one table (AllCalls) I have
    Telephone Number : Short Text
    Call Date : Short Date

    In the other table (AreaCodes) I have
    Code : Short Text
    Area : Short Text


    I am trying to create a query that produces a list:
    Telephone Number, Call Date, Code, Area

    The criteria should be something like:
    If the Telephone Number begins and matches with Code (varies in length), then display it's Area, otherwise display "Not Found".

    The Telephone number is the complete number, i.e. has the area code at the begining.
    This is uk phone numbers, so differ in length.


    Any help much appreicated.

  2. #2
    VBAX Newbie
    Joined
    Sep 2013
    Posts
    1
    Location
    The simplest (and best practice) way to do this would be to have Telephone Number split into two fields (Area Code, Telephone Number) and then simply link the two tables in a FROM statement on Area Code = Code.

    If you can't make this change to your table for some reason, you can use the MID function in a subquery to extract the area code out of the Telephone Number String. The only issue here is if the Code field is of varying length, then you may not know how many characters to extract and compare on.

  3. #3
    VBAX Mentor Marcster's Avatar
    Joined
    Jun 2005
    Posts
    434
    Location
    Thanks for your comments.
    I used Left([Telephone Number],Len([Telephone Number])-6) to find the 6 digit phone number, the numbers left over I'm taking as the Code, which I use to match against AreaCodes.Code.

Posting Permissions

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