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.
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.
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?
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.
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.
Code:
If DCount( "*", "tablename", "0 <> InStr( 1, [userid], '" & trim( inusername ) & "';" ) <> 0 Then
'have found a match
Else
'no matches found
End If
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.
Code:
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