Good morning Forum,
I am using a code that I found on internet to apply filters on a form whose recourdsource is Table1.
I have 10 unbound controls as filters. Nine of them work beautifully. The last one should apply a filter based on a calculated field and I don’t know how to make it work.
Filter Control name: FltStatus (Combo with 3 choices : Expired, In progress or Future
Calculated control: TxtStatus (Calculation based on dates, displays “Expired”, or “In progress” or “Future”)
The code (the first filter works well and is a numeric field, from Table1):
Code:
Private Sub BtnFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.FltLease) Then
strWhere = strWhere & "([Lease] = " & Me.FltLease & ") AND "
End If
If Not IsNull(Me.FltStatus) Then
strWhere = strWhere & "([TxtStatus] = """ & Me.FltStatus & """) AND "
End If
'.... other filters that work fine
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Of course, TxtStatus not originating from Table1, the filter doesn’t work.
How to write the strWhere in this case?
Thank you very much in advance and have a good day all.