Not Me wrote:
Hi,
Is there any criteria I can use in my where clause to say 'anything'? Maybe
like the _ used in some languages?
For my example, I want to use an inline-if, so if a checkbox is ticked I say
where field=true, otherwise just let everything pass through.
For checkboxes I know I can used iif(xxx,true,(true or false)) but I also
wish to use strings, where iif(xxx,yyy,'*') doesn't work - as the search on
'*' doesn't pick up null values that I'd want to keep hold of in outer join
situations..
Let's say you have a form with gobs of combo boxes, option groups, date fields,
etc that will define the filter for a report. You have a command button to
print/preview the report. I usually have a routine to create the filter and a
routine to create a string that deines the filter that is placed in the report
header so the user can take the report and see how the results were arrived at.
Ex:
Dim strWhere As String
Dim strWhereString As String
strWhere = MakeFilter()
Me.WhereSTring = MakeFilterString
Docmd.OpenReport "MyReport",,,strWhere
,,,,
Private Function MakeFilter()
Dim strWhere As String
If Not Isnull(Me.DateFld) Then strWhere = "DateFld = #" & Me.DateFld & "#
And "
If Me.ComboBox > 0 then strWhere = strWhere & "Status = " & Me.ComboBox & "
And "
etc
If strWhere > "" Then MakeFilter = Left(strWhere,len(strWhere)-5) 'remove
And
end function
Private Function MakeFilterString()
Dim strWhere As String
If Not Isnull(Me.DateFld) Then strWhere = "Date Field: " & Me.DateFld &
space(5)
'etc.
MakeFilter = strWhereString
end function
In the report header I create a field with the control source
Forms!ReportFilterForm!WhereString
and it will display the verbose filter created. Prints 1 time only on page.
The filter only displays records that met a criteria in MakeFilter().
The bottom line, filter those that meet a criteria. If you want to include them
all (ex, filter on True, if not true, select ALL) then why bother adding it to
the filter?
If Checkbox = True then strWhere = "[YesNoField] = True And "
If null or false, why add it to the filter?