UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Filter Field    
(Redirected from ClsFilterField)

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

CODE
' 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

Creative Commons License
Filter Field 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 8,285 times.  This page was last modified 19:03, 16 April 2011 by Glenn Lloyd.   Disclaimers