Re: parameter with iif statement in query criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

Doug Glancy wrote:

Win XP Access 2003

In a select query, I want the criteria to be "Is Not Null" if the parameter
is left blank and for the query to not filter on that field. Otherwise I
want to filter based on the parameter. Among many other things, I've tried
this:

Field: Funder
Criteria: IIf([strPassedFunder] Is Null,Is Not Null,[strPassedFunder])

Try using the criteria:

=[strPassedFunder] OR [strPassedFunder] Is Null

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Display if count = 5
    ... criteria line of the field that is being counted, ... And, if you count something in the design grid in a query, ... if you have designed a complicated filter. ...
    (microsoft.public.access.queries)
  • Re: other table that stores data differently than ADO.Net datatable?
    ... >> would suggest making some sort of a "search" type of screen and filter ... >> criteria for your where clause, that will help slim down your list. ... >>> query the database everytime the user does a scroll (or page up/page ... >>> will display the next N records once the user has reached the last ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Near Suicidal!
    ... box to filter by pressing a button on the form where the list ... By pressing on the button I want to pass a parameter (or ... to the the query the list's rowSource is based on. ... example) into the fldChargeType's criteria row and ...
    (microsoft.public.access.formscoding)
  • Re: Query only showing specific records - i need all - please help
    ... If the data is in the table but not in the form, you may have a filter on ... If the form is based on a query, then, when you look at the ... have a criteria in the query that filter out those records. ... You could also have use a Format to format the NULL values as displaying ...
    (microsoft.public.access.queries)
  • Re: access 2003
    ... the "ChooseJob_AfterUpdate Event" uses this filter ... the form to show a new set of records: For, as you know, the query points to ... the two combo boxes for criteria information. ...
    (microsoft.public.access.conversion)