The SQL for your condition should be something like:
WHERE auditor = Forms!NameOfForm!NameOfComboBox
OR Forms!NameOfForm!NameOfComboBox = "(All)"
OR (Forms!NameOfForm!NameOfComboBox = "(Blank)"
AND auditor = Null)
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
I have a continuous form that is based off of a table, each record has
12 fields, 10 of which I wish the user to be able to filter on.
The form has 10 combo boxes in the form header which are unbound and
have Row Sources like this;
SELECT DISTINCT tblAuditActions.auditor
FROM tblAuditActions
WHERE len(tblAuditActions.auditor)>0
UNION Select "(All)" as Bogus From tblAuditActions
UNION Select "(Blank)" as Bogus2 From tblAuditActions
ORDER BY [auditor];
tblAuditActions is the table the form is base on by the way.
What I'm aiming for is something similar to excels auto filter, where
when the form opens all the drops downs are set to "(All)" and every
record is shown, and when a user selects a value to filter the form re-
queries and the other drop downs re-query to only show values that
appear on the filtered list of records.
I've got to the point where I can filter the form but only 1 field at
a time, but I can't get the "(Blank)" selection from my union query to
only bring up fields with a blank entry.
Any advice or pointers you can offer will be hugely appreciated!
Thanks!!- Hide quoted text -
- Show quoted text -
Thanks Doug,
That shunted me in the right direction and I've got the filter working
this morning
However, the filter works on the fields that are text data types, but
I can't for the life of me get it to work on the fields that are
date. I have tried all sorts of syntax in vb in a trial and error
type approach but not getting any closer to solving this one.
I think at first it wasn't working because the union was adding text
value (All) and (Blank) into a date field so I added a hidden column
in the combo box that I set as the bound column which unioned the all
to a random date (01/01/1900) and for blank i used (02/01/1900) so
then atleast i can seperate then in VB with an IF.
I know the solution I have is more than likely extremely long winded
and probably could be achieved with a lot less effort but I just need
to get something out that works and the workings behind it are far
less important.
I'll post below what I have so far in case it helps you understand
where I'm at.
Function FilterString(cboDate As Date, cboRef As String, cboType As
String, cboAuditor As String, cboProcessStep As String, cboProject As
String, cboSubProject As String, cboActionee As String, cboStatus As
String, cboReviewDate As Date, cboForecastDate As Date,
cboCompletionCloseDate As Date)
Dim strCboDate As String
Dim strCboRef As String
Dim strCboType As String
Dim strCboAuditor As String
Dim strCboProcessStep As String
Dim strCboProject As String
Dim strCboSubProject As String
Dim strCboActionee As String
Dim strCboStatus As String
Dim strCboReviewDate As String
Dim strCboForecastDate As String
Dim strCboCompletionCloseDate As String
Dim StrFilter As String
If cboDate = #1/1/1900# Then
strCboDate = "'' AND"
Else
strCboDate = "[Date] = #" & cboDate & "# AND"
End If
If cboRef = "(All)" Then
strCboRef = " '' AND"
Else
strCboRef = " Ref = '" & cboRef & "' AND"
End If
If cboType = "(Blank)" Then
strCboType = " Type Is Null AND"
Else
If cboType = "(All)" Then
strCboType = " '' AND"
Else
strCboType = " Type = '" & cboType & "' AND"
End If
End If
If cboAuditor = "(Blank)" Then
strCboAuditor = " Auditor Is Null AND"
Else
If cboAuditor = "(All)" Then
strCboAuditor = " '' AND"
Else
strCboAuditor = " Auditor = '" & cboAuditor & "' AND"
End If
End If
If cboProcessStep = "(Blank)" Then
strCboProcessStep = " [Process Step] Is Null AND"
Else
If cboProcessStep = "(All)" Then
strCboProcessStep = " '' AND"
Else
strCboProcessStep = " [Process Step] = '" & cboProcessStep &
"' AND"
End If
End If
If cboProject = "(All)" Then
strCboProject = " '' AND"
Else
strCboProject = " Project = '" & cboProject & "' AND"
End If
If cboSubProject = "(All)" Then
strCboSubProject = " '' AND"
Else
strCboSubProject = " [Sub Project] = '" & cboSubProject & "' AND"
End If
If cboActionee = "(Blank)" Then
strCboActionee = " Actionee Is Null AND"
Else
If cboActionee = "(All)" Then
strCboActionee = " '' AND"
Else
strCboActionee = " Actionee = '" & cboActionee & "' AND"
End If
End If
If cboStatus = "(Blank)" Then
strCboStatus = " Status Is Null AND"
Else
If cboStatus = "(All)" Then
strCboStatus = " '' AND"
Else
strCboStatus = " Status = '" & cboStatus & "' AND"
End If
End If
If cboReviewDate = #1/1/1900# Then
strCboReviewDate = " [Review Date] Is Null AND"
Else
If cboReviewDate = #2/1/1900# Then
strCboReviewDate = " '' AND"
Else
strCboReviewDate = " [Review Date] = '" & cboReviewDate & "'
AND"
End If
End If
If cboForecastDate = #1/1/1900# Then
strCboForecastDate = " [Forecast Date] Is Null AND"
Else
If cboForecastDate = #2/1/1900# Then
strCboForecastDate = " '' AND"
Else
strCboForecastDate = " [Forecast Date] = '" & cboForecastDate
& "' AND"
End If
End If
If cboCompletionCloseDate = #1/1/1900# Then
strCboCompletionCloseDate = " [Completion/Close Date] Is Null"
Else
If cboCompletionCloseDate = #2/1/1900# Then
strCboCompletionCloseDate = " ''"
Else
strCboCompletionCloseDate = " [Completion/Close Date] = #" &
cboCompletionCloseDate & "#"
End If
End If
StrFilter = strCboDate & strCboRef & strCboType & strCboAuditor &
strCboProcessStep & strCboProject & strCboSubProject & strCboActionee
& strCboStatus & strCboReviewDate & strCboForecastDate &
strCboCompletionCloseDate
FilterString = StrFilter
End Function
example of combo after update event (all the other combos are the
same):
Private Sub cboCompletionCloseDate_AfterUpdate()
strToFilter = FilterString(Me.cboDate, Me.cboRef, Me.cboType,
Me.cboAuditor, Me.cboProcessStep, Me.cboProject, Me.cboSubProject,
Me.cboActionee, Me.cboStatus, Me.cboReviewDate, Me.cboForecastDate,
Me.cboCompletionCloseDate)
Me.Filter = strToFilter
Me.FilterOn = True
If Me.cboCompletionCloseDate.Value <> #1/1/1900# Then
Me.cboCompletionCloseDate.BorderWidth = 2
Me.cboCompletionCloseDate.BorderColor = 16711680
Else
Me.cboCompletionCloseDate.BorderColor = 0
Me.cboCompletionCloseDate.BorderWidth = 0
End If
End Sub
and an example of the row source of the combo:
SELECT DISTINCT format(tblAuditActions.[completion/close date],"yyyy/
mm/dd") as ccd2 ,tblAuditActions.[completion/close
date],tblAuditActions.[completion/close date]
FROM tblAuditActions
WHERE len(tblAuditActions.[completion/close date])>0
UNION Select "(Blank)" as Bogus, "(Blank)" as Bogus2, "01/01/1900" as
bogus3
From tblAuditActions
UNION Select "(All)" as Bogus, "(All)" as Bogus2, "02/01/1900" as
bogus3
From tblAuditActions
ORDER BY ccd2;
column count: 3
column widths: 0cm;2.547cm;0cm
bound column: 3
Sorry about the lengthy post, and thanks for any further pointers you
can provide!!
