Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Mions, Rh
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing All Spaces from a query result

    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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing All Spaces from a query result

    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 < 48 Or intCheck > 122 Then
    Else
    If (intCheck > 57 And intCheck < 65) Or (intCheck > 90 And intCheck < 97) Then
    Else
    CleanString = CleanString & Chr(intCheck)
    End If
    End If
    intCode = intCode + 1
    Loop
    End Function

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing All Spaces from a query result

    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
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing All Spaces from a query result

    Dear All,
    Access 2000 no sr

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing All Spaces from a query result

    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?
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing All Spaces from a query result

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing All Spaces from a query result

    Good question. I'll research it when I have time.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing All Spaces from a query result

    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)
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing All Spaces from a query result


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •