Re: Telephone Number Format

From: Jack MacDonald (jackMACmacdonald_at_telus.net)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 15:28:09 GMT

Much better...

On Thu, 30 Dec 2004 11:10:15 +0000, Peter R. Fletcher
<pfletch(at)fletchers(hyphen)uk.com> wrote:

>I agree about working on a COPY of the database, but that's an awful
>lot of work to handle a fairly simple substitution/update! Your
>approach guarantees that you will only change "perfectly formed" phone
>numbers in the old format, but I would regard that as a negative
>rather than a positive.
>
>I would write a very simple Function to strip out all non-numerics
>from a passed string, e.g.
>
>Function MyStrip(strInput As String) As String
>
>Dim strTemp As String
>Dim strChar As String
>Dim intCount As Integer
>
>strTemp = Trim(strInput)
>MyStrip = vbNullString
>
>For intCount = 1 To Len(strTemp)
> strChar = Mid$(strTemp, 1, 1)
> If IsNumeric(strChar) Then MyStrip = MyStrip & strChar
> strTemp = Mid$(strTemp, 2)
>Next intCount
>
>End Function
>
>I would then use a single Update Query to replace the old format phone
>numbers with MyStrip(OldNumber). No data will be lost - indeed, if the
>old number was malformed in a way that did not affect its numeric
>content (extra spaces, missing parens, etc) it will be converted
>correctly. Missing numbers will not be replaced, of course (!), but
>they wouldn't be by any "automatic" approach.
>
>On Thu, 30 Dec 2004 06:36:10 GMT, Jack MacDonald
><jackMACmacdonald@telus.net> wrote:
>
>>I would work on a COPY of the database and update the field
>>incrementally with a series of Update queries.
>>
>>- Using the query grid, set a criterion where Mid(PhoneNumber,1,1)="("
>>- satisfy yourself that the selected records are indeed the ones that
>>you want to modify
>>- change the query to an Update query while maintaining the same
>>selection criterion
>>- update PhoneNumber field with an expression: Mid(PhoneNumber,2)
>>- revert to a Select query and confirm that none of the records
>>commence with "("
>>
>>modify the criterion to find ")" in position 4
>>- etc
>>- replace the existing PhoneNumber with Left(PhoneNumber,3) &
>>Mid(PhoneNumber,5)
>>
>>modify the criterion to find "-" in position 4
>>- etc
>>
>>modify the criterion for Instr(1,PhoneNumber,"(")>0 OR
>>Instr(1,PhoneNumber,")")>0 OR Instr(1,PhoneNumber,"-")>0 to confirm
>>that you have found all of the extraneous characters.
>>
>>On Wed, 29 Dec 2004 21:49:02 -0800, "The Grape Hunter"
>><TheGrapeHunter@discussions.microsoft.com> wrote:
>>
>>>Have table of about 11,000 records. Table design was NOT set up to store
>>>phone numbers without format. They have been entered and stored in (333)
>>>444-5555 format. Query works fine if they are asked for in the displayed and
>>>stored format. The users fumble with the correct entry for the query and
>>>would rather just key in the raw 10 digit sequence. ( ie 3334445555) for the
>>>query. I have changed the field definitions in the table design, but that is
>>>not fixing the stored data. If I go back and retype a phone number in, that
>>>entry becomes accesible to the raw number query. How could I automate or fix
>>>in some other fashion how these phone numbers were previously stored?
>>>
>>>Thanks for your great help.( I know the answer is out there!)
>>
>>
>>**********************
>>jackmacMACdonald@telusTELUS.net
>>remove uppercase letters for true email
>>http://www.geocities.com/jacksonmacd/ for info on MS Access security
>
>
>Please respond to the Newsgroup, so that others may benefit from the exchange.
>Peter R. Fletcher

**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Relevant Pages

  • Re: parse cell contents
    ... extract the date I will get the yyyy-mm-dd format. ... What we will do is first look at the first word in the string. ... Dim ValidEntry As Boolean ... 'Set the pattern by using the Pattern property. ...
    (microsoft.public.excel.programming)
  • Re: Setting field properties in code
    ... format that the user has defined in the Windows Control Panel, ... >> Sub StandardProperties(strTableName As String) ... >> Dim tdf As DAO.TableDef 'Table nominated in argument. ... >> Dim ind As DAO.Index ...
    (comp.databases.ms-access)
  • Re: Zellformatierung vQ=B6llig_selbstherrlich_=2897=29?=
    ... indem Du das Format selbst exakt defninierst. ... >> Public Sub Sammeln(Name As String) ... >> Dim EingangAs String ... Eine Alternative ist auch der Makro-Recorder während Du eine Zelle als Text ...
    (microsoft.public.de.excel)
  • Re: tab in userform locks program
    ... > I am a VBA newbie and have not been using DIM in my code so it is ... declare a variable at the top of a standard module using Private (module ... You may notice that I used Format$ instead of Format. ... but Format$ returns a String whereas ...
    (microsoft.public.excel.programming)
  • Re: Spellnumber - USD/AFa
    ... >>> format it returns me Afghani which is correct and also when I change ... >> Public Function DollarsAFAAs String ... >> Dim DecimalPoint As Long ... >> Dim TestValue As Long ...
    (microsoft.public.excel.misc)