By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

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

P: 12
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!
Jan 8 '14 #1
Share this Question
Share on Google+
2 Replies


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

Either in the criteria section of the query builder use:

Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 8 '14 #2

NeoPa
Expert Mod 15k+
P: 31,495
Morgana, there are a couple of ways of handling this generally :
  1. For string values you can say :
    Expand|Select|Wrap|Line Numbers
    1. WHERE ([Field] Like ('*'+[Parameter]) & '*')
  2. For numeric values you can say :
    Expand|Select|Wrap|Line Numbers
    1. 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.
Jan 9 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.