Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Conditional formatting of UK telephone numbers

Posted on 2008-06-26
6
440 Views
Last Modified: 2013-12-25
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:
For 01
If number = 011? - format = xxxx xxx xxxx
If number = 01?1  format = xxxx xxx xxxx
Else  format = xx xxx xxx xxx
For 02
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?
0
Comment
Question by:BorderBox
  • 4
  • 2
6 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 300 total points
ID: 21875042
you will need a function to do that, place this codes in a module

function getCorrectFormat(sTel as string) as string

select case left(stel,2)
     case "01","08"
         getCorrectFormat=Format(stel,"@@@@ @@@ @@@@")
     case "02","03","05"
         getCorrectFormat=Format(stel,"@@@ @@@@ @@@@")

     case "07"
         getCorrectFormat=Format(stel,"@@ @@@ @@@ @@@")



end select

end function
0
 

Author Comment

by:BorderBox
ID: 21877353
That looks really promising .   Am I right in thinking that just changes the format and that the data is still just a string of numbers without spaces?
But please forgive my ignorance.  How do I apply that module to a control on a form?   Can I also apply that module globally to all telephone fields in a database?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 300 total points
ID: 21877385
<Am I right in thinking that just changes the format and that the data is still just a string of numbers without spaces?>
 yes, if you don't update the field using the function,
<Can I also apply that module globally to all telephone fields in a database?>
Yes

to test - create a query like this

select  OfficePhone, getCorrectFormat([OfficePhone]) from TableName
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:BorderBox
ID: 21891545
Thanks.   I am definitley making progress.

How do I apply that module to a control on a form?
How would I use the function to update the field rather than just formatting it?
The test for 01 numbers involves four characters.  How do I combine that with the test for two characters.   I have tried converting the string to 4 and using "?" and "#" but without success.
0
 

Author Comment

by:BorderBox
ID: 21900665
[Groan] Sorry about the mistype on "definitely".

Any thoughts on the three queries?  I would be grateful for your help.
0
 

Accepted Solution

by:
BorderBox earned 0 total points
ID: 22041812
No further response so I have:
1    Used the following code
Function getPhoneFormat(sTel As String) As String
Select Case Left(sTel, 4)
     Case "0110" To "0119"
        getPhoneFormat = Format(sTel, "@@@@  @@@  @@@@")
     Case "0121", "0131", "0141", "0151", "0161", "0171", "0181", "0191"
        getPhoneFormat = Format(sTel, "@@@@  @@@  @@@@")
     Case "0200" To "0699"
         getPhoneFormat = Format(sTel, "@@@  @@@@  @@@@")
     Case "0800" To "0899"
         getPhoneFormat = Format(sTel, "@@@@  @@@  @@@@")
     Case Else
         getPhoneFormat = Format(sTel, "!&& &&&  &&&  &&&&&")
End Select
End Function

2   Since I don't know how to get the module function to work on exit from a form control I have arranged for the Autoexec macro to edit all the telephone fields by deleting all the spaces and then reapplying the formatting module.  Now even if the user makes a mess of the formattiing it will be corrected on the next opening.   No doubt very clumsy but the best I could manage.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question