Converting Nulls?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi again --

First off -- this forum has been so invaluable to me. A big thanks to all
who have helped me already!

Now, the question at hand: Is there a way to have a query accept a NULL
parameter, and yet return all values for that field? Specifically, I have a
form that allows users to enter a numeric parameter via a combo box. I take
that numeric and run a query against it and return all appropriate values.

However, if someone decides not to choose a value in the combo box, a NULL
is passed into the query and nothing is returned. Is there a way to have the
query recognize a NULL value and instead return ALL numerics instead?

Thanks in advance!
 
There are klutzy ways to get this to work, such as:
IIf([Forms].[Form1].[Textbox1] Is Null, Is Null Or Like "*",
[Forms].[Form1].[Textbox1])

However, it is usually better to simply avoid the condition if the text box
is left blank. You can do that by building a string dynamicallly, and then
assigning it to the Filter of your form, the WhereCondition of your
OpenReport, the OpenRecordset(), or wherever you are targeting this.
 
You can set the WHERE clause with something like:

..... WHERE ([YourField] = [Forms]![YourForm]![ComboBox])
OR ([Forms]![YourForm]![ComboBox] Is Null)
 
Dear Bad:

The simple answer is to use the obvious method of making the test, but
add:

OR [Forms]![FormName]![ControlName] IS NULL

using your FormName and ControlName. Put parens around the existing
test and this test:

WHERE (SomeColumn = [Forms]![FormName]![ControlName]
OR [Forms]![FormName]![ControlName] IS NULL)

There are conditions where a control can look like it does when its
value is NULL, but be an empty string. They look identical to the
user, so you may need to modify the test:

WHERE (SomeColumn = [Forms]![FormName]![ControlName]
OR Nz([Forms]![FormName]![ControlName], "") = "")

With this form, you should have no problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top