Conditional formatting of UK telephone numbers
Posted on 2008-06-26
I manage a membership database for a professional organisation. There are five different telephone fields (OfficePhone, Fax, DirectPhone, CellPhone and HomePhone). For consistency I keep all the numbers as plain strings of continuous numbers but I use an input mask (00\ 000" - "000\ 000;;_) on the tables, queries and forms to format the numbers like 01 234 - 567 890. This works for many of the numbers but unfortunately ih the UK we have different formats (and number lengths) in different parts of the country and I get the occasional complaint from people who do not like my universal format. For example, Londoners like to be 02x xxxx xxxx and Geordies like to be 0191 xxx xxxx, and so on.
As far as I can see the conditional rules are:
If number = 011? - format = xxxx xxx xxxx
If number = 01?1 format = xxxx xxx xxxx
Else format = xx xxx xxx xxx
If number = 02? then format = xxx xxxx xxxx
03 = xxx xxxx xxxx
05 = xxx xxxx xxxx
07 = xx xxx xxx xxx
08 = xxxx xxx xxxx
I am presuming that conditional input masks or conditional formatting are not possible so I will have to hold the numbers as strings containing the spaces which format the numbers.
If that is correct then ideally, what I would like is two things:
1 an EXIT MACRO for edited telephone fields which checks the contents and corrects as necessary (Deleting all spaces and then reinserting as necessary)
2 an UPDATE QUERY which I can run globally on each telephone field once a year (before I do the annual yearbook) to check and correct all the numbers.
My knowledge of Access and programming is not up to these tasks. Can anyone help me solve this issue quickly please?