 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006Creating an In statement with comma delimited list
Howdy! I am using '97 and I need a quick and dirty bit of code that will convert user entry like this: 015,017,023 to: '015','017','023' so that I can use it for an IN statement in a SQL string I'm building on the fly. My SQL builder will create something like "SELECT * from TABLE where FIELD IN('015','017','023')". I'm just brain farting on how to easily accomplish this ',' delimited list. TIA, Jana btw: This group is awesome :) i don't know if the Split() function is available in A97 - but if it is, try this: Dim vals() As String, i As Integer, str As String vals = Split(Me!Text0, ",") For i = 0 To UBound(vals) str = Nz(str, "") & "'" & vals(i) & "'" If Not i = UBound(vals) Then str = str & "," End If Next i str = "SELECT TableName.FieldName FROM " _ & "TableName WHERE FieldName In(" & str & ")" hth > Howdy! > I am using '97 and I need a quick and dirty bit of code that will [quoted text clipped - 12 lines] > > btw: This group is awesome :) Tina: Nope, Split is not a valid funtion in '97 :( But thank you for the input! Jana hmm, okay. hopefully A97 has the Nz(), InStr(), Left(), Right(), and Len() functions available. if so, the following code is pretty ugly, but see if it will work for you: Dim val As String, str As String, i As Integer val = Nz(Me!Text0, "") Do i = InStr(1, val, ",") If i > 0 Then str = Nz(str, "") & "'" & Left(val, i - 1) & "'" Else str = Nz(str, "") & "'" & val & "'" Exit Do End If val = Nz(Right(val, Len(val) - i), "") str = str & "," Loop Until val = "" str = "SELECT TableName.FieldName FROM " _ & "TableName WHERE FieldName In(" & str & ")" hth > Tina: Nope, Split is not a valid funtion in '97 :( But thank you for > the input! > > Jana Tina: Thanks very much, it worked like a charm!!! Jana > Tina: Nope, Split is not a valid funtion in '97 :( But thank you for > the input! There must be hundreds of postings of Split functions for Access 97 and earlier, in the newsgroups. Groups.Google is your frend -- keep experiementing with search arguments. Larry Linson Microsoft Access MVP Larry: Thanks for the suggestion, after seeing the 'Split' function that Tina mentioned, that's EXACTLY what I did! I had searched before posting, but just hadn't known the proper search argument to really find what I needed. Yet another term to file away for future search usage :-) Jana Larry: btw, I found this great posting by Mike Preston in my quest: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q188007& Which has Microsoft equivalent functions for dealing with strings such as Split, Join & Replace. Very handy! Jana |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.