Field       - string containing the name of the field to be filtered
  Value       - string containing the filter criteria
  HandleAs    - integer to indicate the data type of the field to be filtered

  CreateFilter - returns a string that can be assigned as as filter or used as the
                      "Where" argument when a form is opened.

' Filter Field
' http://www.utteraccess.com/wiki/index.php/Filter_Field
' 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  {{{YYYY-MM-DD}}}                    
'
Option Compare Database
Option Explicit

Const cstrModule = "clsFilterField"
' Module: clsFilterField
' DateTime: 31/03/2011 5:23:46 AM
' Author:
' Description:
'   Object used to pass information to filter builder

'---------------------------------------------------------------------------------------
   
Private mstrField As String
Private mvarValue As Variant
Private mintHandleAs As Integer

Public Property Get HandleAs() As Integer
   On Error GoTo HandleExit
   
   HandleAs = mintHandleAs
HandleExit:
End Property
Public Property Let HandleAs(rData As Integer)
   On Error GoTo HandleExit
   
   mintHandleAs = rData
HandleExit:
End Property

Public Property Get Field() As String
   On Error GoTo HandleExit
   
   Field = mstrField
HandleExit:
End Property
Public Property Let Field(rData As String)
   On Error GoTo HandleExit
   
   mstrField = rData
HandleExit:
End Property
Property Get Value() As Variant
   On Error GoTo HandleExit
   
   If IsObject(mvarValue) Then
       Set Value = mvarValue
   Else
       Value = mvarValue
   End If

HandleExit:
End Property
Property Let Value(rData As Variant)
   On Error GoTo HandleExit
   
   mvarValue = rData
HandleExit:
End Property
Property Set Value(rData As Variant)
   On Error GoTo HandleExit
   
   Set mvarValue = rData
HandleExit:
End Property

Public Function CreateFilter() As String
' Procedure: fCreateFilter
' DateTime: 31/03/2011 5:26:21 AM
' Author:
' Description: create a filter
'--
   
   Const cstrProcedure = "fCreateFilter"
   Dim strResult As String
   
   On Error GoTo HandleError
   
   'create the base string
   strResult = "[" & mstrField & "] = "
   
   'convert the value to the correct date type
   Select Case mintHandleAs
   
   Case vbInteger
       mvarValue = CInt(mvarValue)
   Case vbSingle
       mvarValue = CSng(mvarValue)
   Case vbDouble
       mvarValue = CDbl(mvarValue)
   Case vbLong
       mvarValue = CLng(mvarValue)
   Case vbCurrency
       mvarValue = CCur(mvarValue)
   Case vbDecimal
       mvarValue = CDec(mvarValue)
   Case vbByte
       mvarValue = CByte(mvarValue)
   Case vbBoolean
       mvarValue = CBool(mvarValue)
   Case vbDate
       mvarValue = CDate(mvarValue)
       
   End Select
   
   Select Case TypeName(mvarValue)
   'apply the correct delimiters for the actual data type
       Case "String"
           strResult = strResult & "'" & mvarValue & "'"
           
       Case "Date"
           strResult = strResult & "#" & mvarValue & "#"
       
       Case Else
           strResult = strResult & mvarValue
   End Select
   

   
   CreateFilter = strResult
   
HandleExit:
   
   Exit Function

HandleError:
   ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure
   Resume HandleExit
End Function
'
' Demonstration Function
'
Public Sub sDemoclsFilterField()
' Procedure: sDemoclsFilterField
' DateTime: 06/04/2011 5:43:20 AM
' Description: demonstrate the use of class clsFilterField
'   to create a filter where clause from a combo box selection
'
' typically the value to be included in a filter will come from
' a user selection in a combobox using the text property of the combo
'--
   
   Const cstrProcedure = "sDemoclsFilterField"
   
   Dim oFilterField As clsFilterField
   
   On Error GoTo HandleError
   Set oFilterField = New clsFilterField
   
   'text type field
   With oFilterField
       .Field = "NameLast"
       .Value = "Johnson"
       Debug.Print .CreateFilter
   End With
   
   'text type containing only digits that should be treated as text
   With oFilterField
       .Field = "InvoiceNo"
       .Value = "013862"
       Debug.Print .CreateFilter
   End With
   
   'text type containing only digits that should be treated as a number
  With oFilterField
       .Field = "PrimaryKey"
       .Value = "123869"
       .HandleAs = vbLong
       Debug.Print .CreateFilter
   End With
   
   'text type containing a date
   With oFilterField
       .Field = "HireDate"
       .Value = DateSerial(2011, 4, 6)
       .HandleAs = vbDate
       Debug.Print .CreateFilter
   End With
HandleExit:
   
   Exit Sub

HandleError:
   ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure
   Resume HandleExit
End Sub


Private Sub ErrorHandle(Err As Long, Erl As Long, source As String)
' Procedure: ErrorHandle
' DateTime: 06/04/2011 6:49:55 AM
' Author: simple error handler for demo purposes
' Description:
'--
   
   Const cstrProcedure = "ErrorHandle"
   
   Debug.Print "Error " & Err & " at "; Erl & " in " & cstrModule & "." & cstrProcedure

End Sub