Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enumerating the Elements of a Type (Access 2K VBA)

    I've got a user defined type that contains a mix of ints and strings. Some of the string contain Null characters that I want to get rid of. What I'd like to do is pass the variable containing that type to a function that steps thru each element of the type and, if a string, replaces the Nulls with spaces. Something like the For...Each loop for a collection. Is this possible?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    Are you actually wanting to update the underlying data record, or are you creating an output file of some sort where you want to use spaces instead of nulls? The reason for the question is that there are different answers depending on what you are trying to do.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    I need to undate the underlying data record. What's happening is that this is legacy data that's being massaged a bit and loaded into Access tables.

    HTH

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

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    When you say some of the strings contain Null characters, what exactly are you talking about. Are you talking about empty fields in legacy data or what? Since string variables in Access can't contain Nulls, I'm not sure what you're describing.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    I'm talking about vbNullChars, characters with an ASCII value of 0 (zero). The Trim function won't strip them or my problem would be solved.

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

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    Trim won't remove them because they are zero length. You can use Replace() to search specifically for vbNullChar and replace it with "" (an empty string). Would that work?
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    Did you try using Replace function to replace Null characters with Null strings? Example:

    Function ReplaceNullChar(ByVal strText As String) As String

    ReplaceNullChar = Replace(strText, vbNullChar, vbNullString, , , vbBinaryCompare)

    End Function

    Sub TestReplaceNullChar()

    Dim s As String

    s = "ABC"
    Debug.Print s, Len(s), LenB(s)

    s = "A" & vbNullChar & "B" & vbNullChar & "C" & vbNullChar
    Debug.Print s, Len(s), LenB(s)

    s = ReplaceNullChar(s)
    Debug.Print s, Len(s), LenB(s)

    End Sub

    Test output:

    ABC 3 6
    A B C 6 12
    ABC 3 6

    If there are some ANSI vs Unicode issues with this data you are importing, recommend investigate use StrConv function to convert to/from Unicode (VB strings are Unicode format, which is why length in bytes (LenB function) will always be twice the length in characters (Len function)).

    HTH

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    Thanks, but I already know how to strip the nulls. My question is regarding the ability to step throught the elements of a user-defined type in a fashion similar to the For...Each construct for collections.

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

    Re: Enumerating the Elements of a Type (Access 2K VBA)

    For Each only applies to a collection object or an array. A user-defined type is neither one. The elements of the type are a special case and I know of no way to enumerate them like that.
    Charlotte

Posting Permissions

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