Phone Numbers

[Marcster]

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.

[Coxy]

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.

[Marcster]

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.