Why does Access treat a phone number as text?

Q I have built a little database with a field for phone numbers (Access 2007) but when I enter a number like 02222 222222 a message pops up to say ‘The value you entered does not match the Number data type in this column’ and one of the options it offers is ‘Convert the data in this column to the Text data type’.

Why does it want to do this rather than allow me to store numbers in a Number field?

Bill ‘Numerate’ Watkins

A You and Access are both right here: phone numbers are made up of numbers but the difference is in the fact that, although numerical, we never treat phone numbers mathematically. We don’t add two numbers, or work out the average phone number in our address books (though it’s an interesting idea – if you dialled the average would you open up a conference call to them all at the same time?)

If we treat phone numbers as text, as Access wants us to, we gain several advantages. We can add the symbols and spaces that make the numbers more easily readable, like 02222-222222 or 02222 222222. (Incidentally it is the space in your example phone number that is alerting Access to the fact that the numbers are not going to be treated mathematically.)

We can also use leading zeros, which are not displayed by a Number data type, and leading ‘+’ for numbers in international dialling format (+44 2222-222222). Another benefit is that it is easy to find all such numbers because we can search for every entry beginning with +.

My advice is to implement the change of data type suggested by Access: it has advantages, no disadvantages and treating phone numbers as text is a good rule when building a database.