Access 2007 - Select Query w/User-entered Parameters - "Is Null" or "Is Not Null"

[morganaj]

Hello Ladies and Gents,

I have created a query in MS Access 2007. I want to be able to have the user define more than one parameter. If I have only one parameter AKA Year or PMC Name, the select query runs fine. But, I would like the user to have the option of defining more than one.

But wait, its more complicated. I also want the user to be able to not define the parameter (leave it blank) to retrieve all the years or all the consultants name's.

This is what I have as a query:
Query Help.pdf

I don't know where I am going wrong... I guess the SQL might help, but it is a complex query with ignoring null fields and doing some math. It's less complicated than it looks...

SQL-Problem.txt

Any help would be greatly appreciated.
Thanks!

[morganaj]

I figured this out by crawling around the depths of the Internet.

Either in the criteria section of the query builder use:

Like Iif(IsNull([PARAMETER NAME TO POP]), "*",[PARAMETER NAME TO POP])

This states that if the Parameter is null to use a wildcard to return all fields, else use what was entered by the user.

You can also edit in the WHERE clause of the SQL:

WHERE FieldtoFilter Like Iif(IsNull([Parameter]), "*", [Parameter])

Thought if anyone else was interested in this topic they might like the answer.

I am using this type of query to make a dynamic report.

[NeoPa]

Morgana, there are a couple of ways of handling this generally :

1. For string values you can say :
   

   WHERE ([Field] Like ('*'+[Parameter]) & '*')

2. For numeric values you can say :
   

   WHERE ([Field]=Nz([Parameter],[Field]))

In the first example, for strings, if [Parameter] is null then the right side of the equation resolves to '*'. Otherwise to '*XXX*'.
In the second example the [Parameter] value is used unless null, in which case [Field] is compared to itself.