Where Is Null return all for Currency Range

[tholsey]

I have a form to select filtering criteria for a query to produce custom reports (attachment). There are 14 different criteria that can be used by the user, each more specifically reducing the results and "Is Null" for the fields to be ignored utilizing Wildcards "*". 3 criteria are ranges, one for Dates, one for Estimated Cost Impact and one for Estimated Schedule Impact. I'm using a SQL Select Query and all fields work as expected EXCEPT the Estimated Cost Impact and Estimated Schedule Impact ranges.

I am able to correctly run the Date Range filter, since a Date is automatically entered with a record, by setting up an IIf(Is Null) which resolves to #1/1/1900# for Start and #1/1/2100# Finish when Null is True. There are no Null values in the records dataset.

However, for the currency and schedule record values, the fields can be left Null. What I have tried so far (only WHERE clauses for simplicity, full SQL screenshot attached)...

WHERE ((tbl1LLRegister.EstFinancialImpact) BETWEEN IIF(tbl4QueryLL.EstFinancialImpactLow IS NULL,0,tbl4QueryLL.EstFinancialImpactLow) AND IIF(tbl4QueryLL.EstFinancialImpactLow IS NULL,100000000,tbl4QueryLL.EstFinancialImpactLow))

That produces the report with results when utilized, but EXCLUDES all records with Null values at all times from the record source tbl1LLRegister.EstFinancialImpact. I want all records with Null if these filters are ignored.

I then tried, rather than setting Min/Max values in the True resolve, utilizing "*" in the True statement of the Clause. Written as:

WHERE ((tbl1LLRegister.EstFinancialImpact) BETWEEN IIF(tbl4QueryLL.EstFinancialImpactLow IS NULL,"*",tbl4QueryLL.EstFinancialImpactLow) AND IIF(tbl4QueryLL.EstFinancialImpactLow IS NULL,"*",tbl4QueryLL.EstFinancialImpactLow))

However, when I try to use Wildcards in the True statement, I get an error when the Query runs for a "Data type mismatch". The form used to enter values to "tbl4QueryLL", "tbl4QueryLL" itself and "tbl1LLRegister" all are set to Currency and it works with Min/Max values in the True statement, there is only a data mismatch if I try a Wildcard.

Can I not use Wildcards with Currency? What is the best way to write this where I get all records, including Null, when the "filtering" fields are left empty/null?

First time poster, not a developer but enjoy the exercise. Have looked thru the forums for two days now and haven't found my answer.

Thanks!
Trent

 

[Ranman256]

Field Is Null ,is used in query criteria.
IsNull(field) is used in vb. (including IIF)

Instead of making lots of IIFs, use queries,
1 query to pull the Nulls,
1 query to pull the Not null.
Generate the calc fields in them. No IIF needed.