Converting Nulls?

B

Badmojoman

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!
 
A

Allen Browne

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

Van T. Dinh

You can set the WHERE clause with something like:

..... WHERE ([YourField] = [Forms]![YourForm]![ComboBox])
OR ([Forms]![YourForm]![ComboBox] Is Null)
 
T

Tom Ellison

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
 
Top