Attribute VB_Name = "udf_findInSet"
'-------------------------------------------------------------------------------
'File         : udf_findInSet.bas
'               Copyright mpl by ERB software
'               All rights reserved
'               http://wiki.yaslaw.info/dokuwiki/doku.php/vba/functions/findInSet
'Environment  : VBA 2010 +
'Version      : 1.1.0
'Name         : findInSet
'Author       : Stefan Erb (ERS)
''-------------------------------------------------------------------------------
Option Explicit

'/**
' * Analog zu MySQL FIND_IN_SET()
' * Kann in Access vor allem bei nicht normalisierten Tabellen verwendet werden
' * @param  String  Element das gesucht wird
' * @param  String  Das Set von Elementen, mit Komma getrennt
' * @param  String  Das Trennzeichen. Standard: ,
' * @return Integer oder False
' * @example    If find_in_set("d", "a,b,c,d") Then ...
' * @example    SELECT ... WEHRE find_in_set('d', field1)
' */
Public Function findInSet(ByVal iSearch As String, ByVal iSet As String, Optional ByVal iDelemiter As String = ",") As Variant
On Error GoTo Err_Handler
    findInSet = False
    Dim items() As String:  items = Split(iSet, iDelemiter)
    Dim index   As Integer: For index = 0 To UBound(items)
        If Trim(items(index)) = iSearch Then
            findInSet = index + 1
            Exit Function
        End If
    Next index
    Exit Function
Err_Handler:
    findInSet = False
End Function