"Catch all"

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..

Hope that makes sense!
Cheers,
Chris

re: "Catch all"

"Not Me" <Not.Me@faker.fake.fa.ke> wrote in message
news:c0ih0p$t0u$1@ucsnew1.ncl.ac.uk...[color=blue]
> Hi,
>
> Is there any criteria I can use in my where clause to say 'anything'?[/color]
Maybe[color=blue]
> like the _ used in some languages?
>
> For my example, I want to use an inline-if, so if a checkbox is ticked I[/color]
say[color=blue]
> 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[/color]
on[color=blue]
> '*' doesn't pick up null values that I'd want to keep hold of in outer[/color]
join[color=blue]
> situations..[/color]

Note i've tried 'true' but that doesn't seem to have the effect I wish (it's
a bit like '*' in that the null values don't show up...)

Cheers,
Chris

re: "Catch all"

Not Me wrote:
[color=blue]
> 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..[/color]

try as criteria line

Is Null or Like '*'

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

re: "Catch all"

Not Me wrote:
[color=blue]
> 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..
>[/color]

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?