Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Cool Unanswered: Cleaning up data in a table.

    Can someone out there post how to change the case and remove spaces from data in a table? Table name is GlMaster, data in column is named AccountGroup. I want an example of UPPERCASE, LOWERCASE, TITLECASE and PROPERCASE. I also would like an example of removing all extra spaces, an example would be RED BULL 250ml. There are 5 spaces in this example, I would like to see RED BULL 250ml. One more thing, how can you remove an unwanted character from the column. Another example would be the & character, Ben & Jerry, Bartles & James would be Ben Jerry, Bartles James. What would be better would be to replace the & with the word and. Hmmm, any help would be appreciated.
    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    To replace multiple spaces in a row with a single one you can use this function:

    edit: this code is produces incorrect results!
    fixed version here

    Code:
    Public Function ProperSpace(ByRef data As String) As String
        Dim arr
        Dim i As Long
        Dim LastWasSpace As Boolean
        
        'first split into words at each space
        arr = Split(data, " ")
    
        LastWasSpace = True
        For i = LBound(arr) To UBound(arr)
            If arr(i) = "" Then
                If Not LastWasSpace Then
                    'only if the last character was no space
                    'allow it to go through ie one space between words
                    arr(i) = " "
                End If
                LastWasSpace = True
            Else
                LastWasSpace = False
            End If
        Next i
    
        'join our words together again
        ProperSpace = Join(arr, "")
    End Function
    The algorithm runs in linear time opposed to the usual string copy hell so it's also suitable for large strings.

    To replace characters or strings you can use the replace function

    edit: for the upper, lower and propercase conversions you can use the strconv function
    Last edited by Apel; 11-12-04 at 05:15.

  3. #3
    Join Date
    Nov 2004
    Posts
    10

    Cool

    Very, very new to Access. Where does this code go exactly?

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    First create a new module and just copy the code in there and save the module with a name that pleases you. Then you can use the function like any other within your queries. A sample query would be:
    UPDATE some_table
    SET the_field = ProperSpace(the_field)
    WHERE the_field IS NOT NULL

    I don't really know how to explain the same within the query wizard, maybe the help or someone else has better teaching skills.

  5. #5
    Join Date
    Nov 2004
    Posts
    10

    Question

    This did not work;
    I recieved this error Undefined function 'ProperSpace' in expression.
    Hmmm., Help.

  6. #6
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Mhh, did you copy the code into a new module? A class module of a form won't work, you need to create a seperate module.

  7. #7
    Join Date
    Nov 2004
    Posts
    10

    Cool

    Not sure how to create a new module. My database name is dbnc1, my table name in this database is InvMaster, the field I need updated is ItemName. Now this is what I have done, 1st opened database, 2nd choose Module on the left of database window, 3rd new... and so on. Is this right? Attached is my file.
    Attached Files Attached Files
    Last edited by jamesmp3; 11-11-04 at 12:28.

  8. #8
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Ah, I see it now looking at your db. That's a nasty little thing MS did there. You may not name the module equal to the function or you won't be able to call that function. Call the module modProperSpace or whatever and it should work

    edit: except my code is wrong
    corrected code in a sec
    Last edited by Apel; 11-12-04 at 04:54.

  9. #9
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    ok, slower but at least this one works, sorry for that

    Code:
    Public Function ProperSpace(ByRef data As String) As String
        Dim arr
        Dim i As Long
        
        'first split into words at each space
        arr = Split(data, " ")
        
        For i = LBound(arr) To UBound(arr)
            If arr(i) <> "" Then
                arr(i) = arr(i) & " "
            End If
        Next i
    
        'join our words together again
        ProperSpace = Join(arr, "")
    End Function
    Last edited by Apel; 11-12-04 at 05:11.

  10. #10
    Join Date
    Nov 2004
    Posts
    10

    Smile

    Thank you, thank you, thank you. That worked. Again thank you.

Posting Permissions

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