*** Advanced search (Multi- Field) - VBA code ***

[alhabkk]

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 [Date Removed] which mean its Null ( also which mean in my project company / Combo box its ‘Active’ ).
In another hand, if users enter [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’ it will search for nulls/empty column in [Date Removed]
if user chose ‘Inactive’ it will search for filled column in [Date Removed]



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 Me.cboStatus > "" Then
varWhere = varWhere & "[Date Removed] 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

 

[Pat Hartman]

1. I'm not sure what character a ZLS is so I'm not sure I would trust your expressions to be working correctly. I would use either
If Len(yourfield) > 0
or
If yourfield & "" <> ""
2. Formatting a date turns it into a string and that will make it behave like a string rather than a date. Do not format dates when using them for comparison.
3. For many database engines, using LIKE prevents the query engine from using indexes to satisfy the query. Since you are not using any wild cards, your "like" expressions should be replaced with "=" which will be more efficient.

If Me.cboStatus = "Active" Then
    varWhere = varWhere & "[Date Removed] Is Null AND "
Else
    varWhere = varWhere & "[Date Removed] Is Not Null AND "
End If

 

[alhabkk]

1. I'm not sure what character a ZLS is so I'm not sure I would trust your expressions to be working correctly. I would use either
If Len(yourfield) > 0
or
If yourfield & "" <> ""
2. Formatting a date turns it into a string and that will make it behave like a string rather than a date. Do not format dates when using them for comparison.
3. For many database engines, using LIKE prevents the query engine from using indexes to satisfy the query. Since you are not using any wild cards, your "like" expressions should be replaced with "=" which will be more efficient.

If Me.cboStatus = "Active" Then
    varWhere = varWhere & "[Date Removed] Is Null AND "
Else
    varWhere = varWhere & "[Date Removed] Is Not Null AND "
End If

Thanks,
That helped.....It is working......