UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> BuildInClause    


Synopsis

Build an In Clause from a ListBox

CODE
' BuildInClause
' http://www.utteraccess.com/wiki/BuildInClause
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2015-10-23                    
'
'****************************************************
'BuildInClause
'  build an In Clause from selected items in a List Box
' ARGUMENTS
'  ListBoxIn - a list box control
'  FieldName - Optional, Field Name for In Clause Values
'     Default is an empty string
'  IsString - Optional, True forces items to be treated as strings,
'     False, the default value, has the function determining whether _
'     strings are to be used
' RETURNS
'  1. If FieldName is not an empty string, a complete In Clause term of the form
'     "[FieldName] In ( comma delimited list of selected items) "
'  2. If FieldName is an EMPTY string, a comma delmited list of selected items
'  3. An empty string if there are no selected items
' NOTES
'  Any item in list being non-numeric forces all items to be treated
'     as strings (i.e., quotes are place around all items)
'  Setting IsString to true forces items to be treated as strings, _
'     even if all are numeric
'  Most usage is expected to be
Public Function BuildInClause(ListBoxIn As ListBox, _
  Optional FieldName As String = "", _
  Optional IsString As Boolean = False) As String
   
  Dim str As String
  Dim v As Variant
  'item data could be strings containing commas, so temporary delimiter
  Const conItemDelimiter As String = "£¤"
  With ListBoxIn
     For Each v In .ItemsSelected
        'any item being a string means ALL are strings
        If Not IsNumeric(.ItemData(v)) Then IsString = True
        str = str & conItemDelimiter & .ItemData(v)
     Next
   str = Mid(str, Len(conItemDelimiter) + 1)
   If IsString Then
      'add quotes to items
      str = """" & Replace(str, conItemDelimiter, """,""") & """"
   Else
      'just replace item delmiter with ,
      str = Replace(str, conItemDelimiter, ",")
   End If
   End With
   
   If (Len(FieldName) > 0) And (Len(str) > 0) Then
      str = "[" & FieldName & "] In (" & str & ")"
   End If
 
  BuildInClause = str
 
End Function

Creative Commons License
BuildInClause by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 1,373 times.  This page was last modified 21:48, 23 October 2015 by azolder.   Disclaimers