Removing All Spaces from a query result

Topic

New Reply

Hello.

I am pulling name data from one field (Last Name) to then combine with another (Called) to build a third value (which will we use as a login for another system) which is stored in the same table (EMailMailName). The goal is to pull these together to find duplicates before they happen, and then correct them in order to manage the migration to a new Email system.

I have used this query to

UPDATE Email_Pivot SET Email_Pivot.EmailMailName = Left([Email_Pivot].[Called],1)+Left([Email_Pivot].[Last Name],11);

This works fine, except when I have a name with a space, or an apostrophe in it. I looked at the TRIM function, but that removes EXTRA spaces, and I need to remove all of them (plus the other characters).

Is there an way to do this with a query, or do I need to go to VBA? Any suggestions would be welcome for an SQL solution and/or VBA.

Thanks in advance,
John D

Reply | Quote

Replies

This function removes everything from the input string except alphanumeric characters:

Public Function CleanString(InputString As String)
Dim intCode As Integer
Dim intNameLen As Integer
Dim intCheck As Integer
CleanString = “”
intNameLen = Len(InputString)
intCode = 1
Do Until intCode = intNameLen + 1
intCheck = Asc(Mid(InputString, intCode, 1))
If intCheck 122 Then
Else
If (intCheck > 57 And intCheck 90 And intCheck < 97) Then
Else
CleanString = CleanString & Chr(intCheck)
End If
End If
intCode = intCode + 1
Loop
End Function

Reply | Quote

John:

cpod already provided a function for you that’ll do the trick, and well. Thought I’d give you this to boot as I’ve been able to use it to great effect; feed the function the string you want it to search, what you want it to search the string for, and what you want it to be replaced with. As I’ve just used it myself, it’s not particularly well commented, sorry.

Regards,
~Shane
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function ChangeStr(strOrig As String, strOldChar As String, strNewChar As String _
, intMatchCase As Integer) As Variant
‘This substitutes one character or char string with another as
‘designated in the calling function, from KB Q210372
‘intMatchCase defaults to 0 which means case insensitive; set to 1 for case
‘sensitive comparison against strOldChar
Dim Temp As String
Dim Pos As Integer

Temp = “”

If IsNull(strOrig) Then
ChangeStr = Null
Exit Function
End If

If strOldChar = “” Or strOrig = “” Then
ChangeStr = strOrig
Exit Function
End If

Pos = InStr(1, strOrig, strOldChar, intMatchCase)
While Pos > 0
Temp = Temp & Mid$(strOrig, 1, Pos – 1) & strNewChar
strOrig = Right$(strOrig, Len(strOrig) – Pos – Len(strOldChar) + 1)
Pos = InStr(1, strOrig, strOldChar, intMatchCase)
Wend
ChangeStr = Temp & strOrig

End Function

Reply | Quote

Dear All,
Access 2000 no sr

Reply | Quote

That’s OK. When I try it like that, I get a #Name? error. I had never tried using it that way since I always use it in code, and it works fine there. I did run into a problem at first in my test database because I already had a function called Replace (this was old code) and it overrode the VBA string function. Could you have something similar going on?

Reply | Quote

Sorry Charlotte,

I made a mistake. This works fine now.

Public Function ReplaceSpace(StrRemoveSpace As String)

ReplaceSpace = Replace(StrRemoveSpace, ” “, “”, 1, -1, 1)

End Function

I forgot to feed the result back to the function ReplaceSpace. I must have been half asleep!

Why won’t “Replace” work in a text box?

Reply | Quote

Good question. I’ll research it when I have time.

Reply | Quote

Reply | Quote

Custom functions (that is, user-defined functions) won’t work in controls. However, Replace is a VBA function, not a custom function, so I’m not clear on what his explanation actually means. You can’t run Access without the VBA library, so it can hardly be considered an “outsider”.

Replace does have to be passed a value in order to work at all, so the way to use it would be in the afterupdate event of the control txt_RemoveSpace. You would do something like this:

[txt_strRemoved] = _
Replace([txt_RemoveSpace],],” “,””,1,-1,1)

Reply | Quote