De-concatenate String for Comparison

[pooldead]

I have a table of users where, for application reasons, IDs are stored as "user123;user456". I also have a table of their records to store their status from Active Directory. How can I go about splitting that string up at each instance of a semi-colon, then compare that user ID with the status on the separate table? There are strings sized at "user123;user456;user789;" and so on, not just two IDs per string.

 

[theDBguy]

You could try using the Split() function.

 

[Pat Hartman]

I have a table of users where, for application reasons, IDs are stored as "user123;user456"

I would start by fixing that problem. If you can't do that immediately, then use the split() function to separate the string using the ";" as the delimiter. Then you'll need code to loop through the array and do something with each item. Way harder than it needs to be. Normalize the tables to simplify your process.

 

[pooldead]

So I did try using split earlier but I kept getting an error type mismatch, I think because of trying to pass a string variable into the split function expression. Unfortunately, I can’t store the data differently due to how the entire process is managed (which I can’t get into for proprietary purposes). How would I use split to to store each value separately in an array?

 

[theDBguy]

So I did try using split earlier but I kept getting an error type mismatch, I think because of trying to pass a string variable into the split function expression. Unfortunately, I can’t store the data differently due to how the entire process is managed (which I can’t get into for proprietary purposes). How would I use split to to store each value separately in an array?

The Split() function parses the string into individual segments and returns the result in an array. If you show us how you tried to use it earlier, we could help you fix it.

 

[pooldead]

I don’t have it in front of me, but it was basically:

dim strID() as string, rs1 as dao.recordset
StrID = split(“” & rs1.fields(“userid”) & “”), “;”)

where rs1 is how I’m pulling the userids from the table

 

[theDBguy]

I don’t have it in front of me, but it was basically:

dim strID() as string, rs1 as dao.recordset
StrID = split(“” & rs1.fields(“userid”) & “”), “;”)

where rs1 is how I’m pulling the userids from the table

Which line was getting the type mismatch error?

 

[pooldead]

StrID, it has a problem with something in the split, but I couldn’t figure out why

 

[arnelgp]

strID should be a Variant, since the return of Split() is an Array, not String.

 

[MajP]

No StrID() is fine as written. It is an array of strings.
Easy test

Public Sub TestString()
  Dim strID() As String
  strID = Split("Some;string",";")
  Debug.Print strID(0)
End Sub

 

[arnelgp]

Public Sub TestString()
  Dim strID() As String
  strID = Split("Some;string")
  Debug.Print strID(0)
End Sub

so what is the result, Savant? i think you missed something.

OP, you should also check rs1, you haven't opened it yet.

 

[MajP]

"some"
Do you get something else? Regardless it is an array of strings.

 

[The_Doc_Man]

Is the goal simply to verify that the username is in the list without regard to which record matches it?? If so, you can try this, where inusername is the name the user supplied in the login and I don't know the name of the table so I had to make something up. This, or something very similar to this, should work because with the structure you showed us, there is no way to isolate the usernames closer than just saying "it was someone whose username was in this record but I don't know which one."

The DCount (if I got it more or less right) will only count records for which a match is found. Which should be be either 0 or 1. Adding that semicolon in the 3rd argument of the InStr is because you showed us using semicolons between adjacent usernames.

If DCount( "*", "tablename", "0 <> InStr( 1, [userid], '" & trim( inusername ) & "';" ) <> 0 Then
    'have found a match
Else
    'no matches found
End If

InStr function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[theDBguy]

StrID, it has a problem with something in the split, but I couldn’t figure out why

If you can post the actual code you tried to use, we can help you figure out the why.

 

[gemma-the-husky]

I don’t have it in front of me, but it was basically:

dim strID() as string, rs1 as dao.recordset
StrID = split(“” & rs1.fields(“userid”) & “”), “;”)

where rs1 is how I’m pulling the userids from the table

you have an extra ) in this.
assuming userid is a single string with all your users, you would have this

StrID = split(rs1.fields(“userid”), “;”)
or
StrID = split(rs1!userid, “;”)

but if each userid is separate, which I imagine it is, this won't do anything.
well you will just get a split array with one value - strid(0)

if it is a single string with multiple values then, assuming you are opening and using the record set correctly.

dim strID() as string, rs1 as dao.recordset, x as long, s as string
StrID = split(rs1.fields(“userid”),“;”)
s=""
for x 0 to ubound(strid)
   s  = s & strid(x) & vbcrf
next
msgbox s