apply filter in form based on multiple comboboxes

[leanne123]

I have a form with four comboboxes at the top used to filter the form, each one works independantly, now I want them to work in conjunction, for example:
MessageDate, IDOCStatus, SystemStatus and ActionOn are the four fields.

The code below allows me to filter using one combobox at a time:
BEGIN CODE
Me.Filter = "IDOCStatus = " & Me.cboSystemStatus & ""
Me.FilterOn = True
End Sub
END CODE

Now I would like to choose a value from the MessageDate Field that may return 60 records. Out of those 60, if 15 are in 'pending' IDOCSTATUS, I would then like to choose 'pending' from the IDOCSTATUS combobox and have it return only records with the specified date AND with status Pending. I would like to be able to choose any combination of these four boxes to filter the form.

I know I need to dynamically set the filter string, I started with If statements and got stuck on the 'null' issue.

 

[Remou]

What part of Null? Including records that are null? Please post some code.

 

[MajP]

if not trim(me.someControl & " ") = "" then
do something
end if

 

[leanne123]

BEGIN CODE
Sub SetFilter()
Dim FrmFilter As String

Dim varMessageDate As String
If Me.cboMessageDate = Null Then
Me.cboMessageDate = ""
Else
varMessageDate = "date = '" & Me.cboMessageDate & "',"
End If

Dim varActionOn As String
If Me.cboActionOn = Null Then
Me.cboActionOn = ""
Else
varActionOn = "IDOCActionOn = " & Me.cboActionOn & ","
End If

Dim varSystemStatus As String
If Me.cboSystemStatus = Null Then
Me.cboSystemStatus = ""
Else
varSystemStatus = "Status = " & Me.cboSystemStatus & ","
End If

Dim varIDOCStatus As String
If Me.cboStatus = Null Then
Me.cboStatus = ""
Else
varIDOCStatus = "IDOCStatus = " & Me.cboStatus & ""
End If

FrmFilter = varMessageDate & varActionOn & varSystemStatus & varIDOCStatus
END CODE

 

[leanne123]

I just saw error in my code, re-posting, but still does not work, when I debug the me.cboActionOn field, the value is null, when I set the if statement to me.cboActionOn is null, it says invalid use of null, if I set it like me.cboActionOn = null or = "" it adds the field to the filter anyway.

BEGIN CODE:
Sub SetFilter()
Dim FrmFilter As String

Dim varMessageDate As String
If Me.cboMessageDate = "" Then
varMessageDate = ""
Else
varMessageDate = "date = '" & Me.cboMessageDate & "',"
End If

Dim varActionOn As String
If Me.cboActionOn = Null Then
varActionOn = ""
Else
varActionOn = "IDOCActionOn = " & Me.cboActionOn & ","
End If

Dim varSystemStatus As String
If Me.cboSystemStatus = "" Then
varSystemStatus = ""
Else
varSystemStatus = "Status = " & Me.cboSystemStatus & ","
End If

Dim varIDOCStatus As String
If Me.cboStatus = "" Then
varIDOCStatus = ""
Else
varIDOCStatus = "IDOCStatus = " & Me.cboStatus & ""
End If

FrmFilter = varMessageDate & varActionOn & varSystemStatus & varIDOCStatus
Me.Filter = FrmFilter
End Sub
END CODE

 

[Remou]

You can use [ignore]

[/ignore] for code blocks.

Note that you will need single quote delimters (') for text fields.

Sub SetFilter()
Dim FrmFilter As String
Dim varMessageDate As String
Dim varActionOn As String
Dim varSystemStatus As String
Dim varIDOCStatus As String

If Not IsNull(Me.cboMessageDate) Then
'Or Trim(Me.cboMessageDate & "")<>""
   varMessageDate = " AND date = #" & Format(Me.cboMessageDate,"yyyy/mm/dd") & "#"
End If

If Not IsNull(Me.cboActionOn) Then
'Or Trim(Me.cboActionOn & "")<>""
   varActionOn = " AND IDOCActionOn = " & Me.cboActionOn 
End If

If Not IsNull(Me.cboSystemStatus) Then
'Or Trim(Me.cboSystemStatus & "")<>""
   varSystemStatus = " AND Status = " & Me.cboSystemStatus
End If

If Not IsNull(Me.cboStatus) Then
'Or Trim(Me.cboStatus & "")<>""
   varIDOCStatus = " AND IDOCStatus = " & Me.cboStatus 
End If

FrmFilter = Mid(varMessageDate & varActionOn & varSystemStatus & varIDOCStatus,5)

 

[leanne123]

Thank You!! It is working now. Only thing is it doesn't re-filter if I remove a value from one of the fields. For example, if I choose a date, and then pending. If I delete the 'pending' value from status, it doesn't re-filter just on the date.

 

[Remou]

Does the code run? Set a breakpoint after FrmFilter and check if you reach it and what value it contains.

 

[MajP]

at the beginning of your code something like.

me.filter = ""
me.filteron = false
call setFilter
me.filteron = true