I created Advanced search (Multi- Field) form and its work perfect but I want to add a combo box for search [Date Removed] and the options is ‘Active’ and ‘Inactive’
my table include:
[Location]
[Date Installed]
[Installed by]
[EquipmentTag]
[Date Removed]
...
The users may not enter value in [Date Removed] which mean its Null ( also which mean in my project company / Combo box its ‘Active’ ).
In another hand, if users enter value in [Date Removed] which mean it’s not Null ( also which mean in my project company / Combo box its ‘Inactive’ ).
In short, if user chose ‘Active’ in combobox it will search for nulls/empty column in [Date Removed]. And if user chose ‘Inactive’ it will search for filled column in [Date Removed]
please help me on write vba code for a combobox search????
the code is atteched:-
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "\#mm\/dd\/yyyy\#"
varWhere = Null
If Me.txtlocation > "" Then
varWhere = varWhere & "[Location] like " & tmp & Me.txtlocation & tmp & " AND "
End If
If Me.txtDateFrom > "" Then
varWhere = varWhere & "([Date Installed] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Me.txtDateTo > "" Then
varWhere = varWhere & "([Date Installed] <= " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If
If Me.txtInstalledby > "" Then
varWhere = varWhere & "[Installed by] like " & tmp & Me.txtInstalledby & tmp & " AND "
End If
If Me.txtApprovedby > "" Then
varWhere = varWhere & "[Approved by] like " & tmp & Me.txtApprovedby & tmp & " AND "
End If
If Me.txtEquipmentTag > "" Then
varWhere = varWhere & "[EquipmentTag] like " & tmp & Me.txtEquipmentTag & tmp & " AND "
End If
If Me.txtMOC > "" Then
varWhere = varWhere & "[MOC/WO/RFC No] like " & tmp & Me.txtMOC & tmp & " AND "
End If
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
my table include:
[Location]
[Date Installed]
[Installed by]
[EquipmentTag]
[Date Removed]
...
The users may not enter value in [Date Removed] which mean its Null ( also which mean in my project company / Combo box its ‘Active’ ).
In another hand, if users enter value in [Date Removed] which mean it’s not Null ( also which mean in my project company / Combo box its ‘Inactive’ ).
In short, if user chose ‘Active’ in combobox it will search for nulls/empty column in [Date Removed]. And if user chose ‘Inactive’ it will search for filled column in [Date Removed]
please help me on write vba code for a combobox search????
the code is atteched:-
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conJetDate = "\#mm\/dd\/yyyy\#"
varWhere = Null
If Me.txtlocation > "" Then
varWhere = varWhere & "[Location] like " & tmp & Me.txtlocation & tmp & " AND "
End If
If Me.txtDateFrom > "" Then
varWhere = varWhere & "([Date Installed] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Me.txtDateTo > "" Then
varWhere = varWhere & "([Date Installed] <= " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If
If Me.txtInstalledby > "" Then
varWhere = varWhere & "[Installed by] like " & tmp & Me.txtInstalledby & tmp & " AND "
End If
If Me.txtApprovedby > "" Then
varWhere = varWhere & "[Approved by] like " & tmp & Me.txtApprovedby & tmp & " AND "
End If
If Me.txtEquipmentTag > "" Then
varWhere = varWhere & "[EquipmentTag] like " & tmp & Me.txtEquipmentTag & tmp & " AND "
End If
If Me.txtMOC > "" Then
varWhere = varWhere & "[MOC/WO/RFC No] like " & tmp & Me.txtMOC & tmp & " AND "
End If
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
