Numeric comparison and Null values

[AKATemp]

All,

I tried searching the forums for something similar and unfortunately, haven't been able to find the help I need. If someone can point me in the right direction, I'd appreciate it.

I have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is

Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value

The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.

The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows

[COLOR=red]>[/COLOR]IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])

I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.

Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values

[COLOR=red]>[/COLOR]IIf(IsNull([Forms]![MainForm]![Criteria]),[COLOR=red]SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS[/COLOR],[Forms]![Material Finder]![txtPS])

or in other words

If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D

 

[JHB]

Try the below:

Select Blah blah from dbo_temp where (A>textbox Or textbox Is Null) OR (B>textbox Or textbox Is Null) ...

 

[AKATemp]

Hi JHB,

Unfortunately that did not work. It gave me an error saying that the expression is too complicated to evaluate.

How would the criteria look under column A?

(>[Forms]![MainForm]![Criteria]) Or Is Null

 

[JHB]

It is not quiet the way I showed you.
Let's take an example:
You have a table with a field named Quantity in it, and a form named Form1, with a control in the form named CriteriaColumnA.
If you put in a number in CriteriaColumnA then the query shows all number in the field Quantity, greater as the number in CriteriaColumnA.
If nothing is put in CriteriaColumnA, then query will shows all rows with and without a number in the field Quantity.

Then the query looks like below:

SELECT ...., Quantity 
FROM .... 
WHERE (Quantity>[Forms]![Form1]![CriteriaColumnA] OR [Forms]![Form1]![CriteriaColumnA] Is Null);