Ignore punctuation and spaces? 1

[STirone]

Is there an Access SQL command that will allow the query to ignore punctuation and spaces in data?

I want to run a Distinct query against a list of companies, but I'm running into the period-comma-hyphen-in-the-data problem.

Thanks!

 

[MichaelRed]

NO Intrinsic function, but this has been repeatedly discussd in these forums and soloutions provided. The all essientially just parse a string, accumulating the desireable characters and expelling the undesireables. When the whole string is complete, return the desireables list (string):

One such soloution:

Public Function basAlphNum(MystrIn As String) As String

    Dim tmpStr As String
    Dim tmpChr As String
    Dim Idx As Integer

    'Usage:
    '? basAlphNum("nlakwjheo8q734yrb*&(^%kh32b ku7*(9")
    'nlakwjheo8q734yrbkh32bku79


    For Idx = 1 To Len(MystrIn)
        tmpChr = Mid(MystrIn, Idx, 1)     'Get chr
        If (IsNumeric(tmpChr)) Then     'Check for Numeric
            tmpStr = tmpStr & tmpChr    'Numeric, add to temp
         Else
            If ((tmpChr >= &quot;a&quot; And tmpChr <= &quot;z&quot;) _
                Or _
                 (tmpChr >= &quot;A&quot; And tmpChr <= &quot;X&quot;)) Then
                tmpStr = tmpStr & tmpChr    'Numeric, add to temp
            End If
        End If
    Next Idx

    basAlphNum = tmpStr

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over