[AccessD] FW: A2003: Checking for dupe phone numbers

Hi Jim
Very nice - I like the flexibility to dictate the string to remove
I have filed this one away too :-))))

Many thanks

Darren
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Monday, 6 December 2004 4:15 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] FW: A2003: Checking for dupe phone numbers

Hi Darren:

Or better yet, try the following function. This can be used to remove any
character from a string or called from a query.

<code>
Public Function StripInvalidCharacter(ChkText As String, strInvalidCharacter
As String) as String
    Dim lbPosition As Integer
    Dim lbLength As Integer

    lbLength = Len(ChkText)
    lbPosition = InStr(1, ChkText, strInvalidCharacter)

    Do While lbPosition > 0 And lbPosition < lbLength
        ChkText = Left(ChkText, lbPosition - 1) & Mid(ChkText, lbPosition +
1)
        lbPosition = InStr(lbPosition + 2, ChkText, strInvalidCharacter)
    Loop

    StripInvalidCharacter = ChkText

End Function
</code>

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren DICK
Sent: Sunday, December 05, 2004 6:53 PM
To: AccessD
Subject: [AccessD] FW: A2003: Checking for dupe phone numbers



Never mind -  found a function to do it
Dunno who wrote (wasn't me:-))
Apologies to whoever did - Can't give 'em credit


Function OnlyNumbers(text) As String

'Removes ANYTHING that does not fall into 0-9

    Dim s As String, t As String, x As Long

    If Len(Nz(text, "")) > 0 Then
        For x = 1 To Len(text)
            t = Mid(text, x, 1)
            Select Case t
                Case 0 To 9
                    s = s & t
            End Select
        Next x
    End If
    OnlyNumbers = s
 'Msgbox s
End Function



-----Original Message-----
From: Darren DICK [mailto:d.dick at uws.edu.au]
Sent: Monday, 6 December 2004 1:38 PM
To: AccessD
Subject: A2003: Checking for dupe phone numbers

Hi Guys
I'm trying to comparing phone numbers and report dupes.

Basically I get a user to enter a phone number - then check if it is already
in the dB If it is then I go to the record that already has it.

But of course people legitimately put in spaces after area codas and after
the prefixes.
So it becomes tricky to compare

eg 01 1234 5678 is the same number as 0112345678 but a simple dlookup won't
catch it

So I wanna remove all the spaces so I am comparing at least the same
'number'

OR

Do any of you have a method for comparing or routing out dupe phone
numbers??

Many thanks in advance

Darren

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com