deleting dashes & spaces using query

[deekras]

i need to remove all dashes & spaces from a field. sometimes there are spaces, sometimes not.

i would like to do this from a query because i will be exporting the info to excel from the query.

 

[Fornatian]

You can do this in excel using:

=SUBSTITUTE(SUBSTITUTE(D5," ",""),"-","")
where D5 is the cell in question.

Alternatively if you want to do this in access you can write your own function, suchas:

Public Function RejectSpacesAndDashes(TheWord As String) As String
Dim strChar As String
Dim strWord As String
Dim i As Integer
For i = 1 To Len(TheWord)
If Mid(TheWord, i, 1) = " " Or Mid(TheWord, i, 1) = "-" Then
strChar = ""
Else
strChar = Mid(TheWord, i, 1)
End If
strWord = strWord & strChar
Next i
RejectSpacesAndDashes = strWord
End Function

and reference it in a query column header as

RejectSpacesAndDashes([Whatever])

Ian

 

[deekras]

works great! thanks!

 

[raskew]

Function Removeomatic(pstr As String, pchar As String) As String
'*******************************************
'Name: Removeomatic (Function)
'Purpose: Remove specified characters from a string.
'Inputs: from debug window: ? Removeomatic("123-45-6789", "-")
'Output: 123456789
'*******************************************

Dim strHold As String

strHold = RTrim(pstr)
Do While InStr(strHold, pchar) > 0
strHold = Left(strHold, InStr(strHold, pchar) - 1) & Mid(strHold, InStr(strHold, pchar) + 1)
Loop
Removeomatic = strHold
End Function

 

[Fornatian]

I know these things can be done using Instr() but I never can get the hang of it, that's why I tend to cycle through characters.

Ian

 

[raskew]

If it ain't broken, no point in trying to fix it. Just wanted to show a different approach. However, you bring up a good point. What may be absolutely elementary to one programmer is complete gibberish to another, and visa versa.

Best wishes,

Bob

 

[Atomic Shrimp]

Just to add another slant on the issue; I use a function I wrote that, rather than removing specific characters, removes everything except specified characters - here's the function:

Public Function AllowOnly(yourstring As String, Allowed As String)
If Allowed = "" Then Allowed = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intLoop As Integer
For intLoop = 1 To Len(yourstring)
    If InStr(Allowed, Mid(yourstring, intLoop, 1)) > 0 Then
        AllowOnly = AllowOnly & Mid(yourstring, intLoop, 1)
    End If
Next intLoop

End Function

So:
AllowOnly("123ABC456","0123456789") returns "123456"
AllowOnly("123-102","0123456789") returns "123102"
and so on

if you leave the second parameter blank, it defaults to allowing the full alphanumeric set, deleting everything that isn't a letter or number.

HTH

Mike

 

[Fornatian]

Thanks Mike, i was waiting for you to jump in with a top solution!

Raskew - I couldn't agree with you more - I have just finished a VB course which uses other peoples code as a source for a project. As you may have guessed this was very difficult because each programmer has prescriptive solutions to problems - and that's not always clear. Sometimes they are better sometimes they are worse. My main downfall is usually that I assume that the less typing of code there is the less time it takes to process - not always true.

Best Wishes and Thanks For The Opinion.

Ian

 

[datacontrol]

mike's code

Mike,
This looks like exactly the code that I need. I have a column of data that I need to define as a string, I suppose and then call up this code. How would I set this code to fucntion on a particular column of data (save as string?). If you could give me an example, that would be great.