Query crazy?

[MaMister]

Hi all,

I have a master database (mdb) with fax numbers but they are not in proper:

some: +65, (065), 065, 65 and some symbols like ><= and so on.

So I created this few query,

1st, Query to remove duplicate then from this query
2nd, Query to remove space then from this query
3rd, Query to remove +65 then from this query
4rd, Query to remove 65-

and it goes on and on with other things I wanted to remove.

Forgive me to ask. Is there a better way?

 

[WayneRyan]

Ma,

We all inherit "bad" data at times.

If you are dealing totally in queries, you can do something like:

TheRealNumber: Replace(Replace(Replace([YourField], "(", ""), ")", ""), "+", "")

But, that's really hard to read.

You could consider a custom VBA function; especially if there are any more "extraneous" characters.

Need more info,
Wayne

 

[ajetrumpet]

You can also use something like the following to catch almost all of the strange characters and spaces that you might find within your strings.

To give you an idea what to do with these unwanted characters that are interspersed with data, take a look at the following example:
________________________________________________________________________________________________________________

If I wanted to take all of the unwanted characters out of the following string: C*#OM(<P""[L]!@(I)/C:A__T%^^I,\,O$$N

I would first list all of the characters in an ASCII table that are typically not needed:

Function RetAsc(inputChar As String) As Boolean

  Select Case Asc(inputChar)

    Case 32 To 47
      RetAsc = True

        Case 58 To 64
          RetAsc = True

            Case 92 To 96
              RetAsc = True

                Case 126
                  RetAsc = True

                    Case Else
                      RetAsc = False

  End Select

End Function

Then I would loop through the string in question to extract only the characters that are relevant:

function Extraction()

dim strPOS as integer, strTEMP as string

strTEMP = [I]string in question[/I]

strPOS = 1

  Do Until strPOS > Len(strTEMP)

    strCHAR = Mid(strTEMP, strPOS, 1)

      If RetAsc(strCHAR) = True Then
        strTEMP = IIf(strPOS = 1, Right(strTEMP, Len(strTEMP) - 1), IIf(strPOS = Len(strTEMP), _
          Left(strTEMP, Len(strTEMP) - 1), Left(strTEMP, InStr(strTEMP, strCHAR) - 1) & _
            Right(strTEMP, Len(strTEMP) - InStr(strTEMP, strCHAR))))
              strPOS = strPOS
                Else
                  strPOS = strPOS + 1

      End If

  Loop

Extraction = strTEMP

end function