clsFilterField helps build filter and where expressions. An object of this class can be used, for example, to create a filter or where condition using the value that a user selects from a combobox .text property (using the combo’s AfterUpdate event, for example.) Regardless of the actual value of the selection, the combo box .Text property is, of course, text. This class takes care of creating the filter or where expression with the correct delimiters for the field to which it will be applied.

Properties

  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

Methods

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

Synopsis

Help with creating filter and where conditions

' Filter Field
' http://www.utteraccess.com/wiki/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