PARAMETER QUERY FOR IS NULL OR IS NOT NULL

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

Guest

How can I write a parameter that asks the user to specify if a field is null
or is not null?

field: commission (currency)
the criteria is null works just fine in the query design, but not in the
parameter.

Thanks
Sammie
 
Perhaps

(SomeField is Null and [Enter Parameter] = "Is Null")
Or (SomeField is not null and [Enter Parameter] = "Is Not Null")

If you enter Is Null for the parameter you match the first condition and
if you enter Is Not Null for the parameter you match the second condition.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
How can I write a parameter that asks the user to specify if a field is null
or is not null?

field: commission (currency)
the criteria is null works just fine in the query design, but not in the
parameter.
Click to expand...

Assuming you are using strongly typed parameters (and why not) then
here's a couple of suggested approaches (ANSI-92 Query Mode SQL
syntax):

CREATE TABLE Test (
employee_ID INTEGER NOT NULL UNIQUE,
commission_amount CURRENCY
)
;
INSERT INTO Test VALUES (1, 55)
;
INSERT INTO Test VALUES (2, NULL)
;
INSERT INTO Test VALUES (3, 99)
;
CREATE PROCEDURE GetSalesStaff (
arg_commission_amount CURRENCY = NULL
)
AS
SELECT employee_ID, commission_amount
FROM Test
WHERE IIF(commission_amount IS NULL, -922337203685477.5808
, commission_amount) = IIF(arg_commission_amount IS NULL,
-922337203685477.5808, arg_commission_amount)
;

CREATE PROCEDURE GetSalesStaff2 (
arg_commission_amount CURRENCY = NULL
)
AS
SELECT employee_ID, commission_amount
FROM Test
WHERE (commission_amount = arg_commission_amount
OR (commission_amount IS NULL AND arg_commission_amount IS NULL))
;

Jamie.

--
 
I liked this simpler approach, but it didn't work. the is null parameter
didn't return any records, and the is not null returned records that didn't
match the other criteria of my query. I guess working with null values can
be tricky. If there's an obvious answer to this, please let me know.
Otherwise, I'll forget about this idea.
Thanks.
Sammie

John Spencer said:
Perhaps

(SomeField is Null and [Enter Parameter] = "Is Null")
Or (SomeField is not null and [Enter Parameter] = "Is Not Null")

If you enter Is Null for the parameter you match the first condition and
if you enter Is Not Null for the parameter you match the second condition.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How can I write a parameter that asks the user to specify if a field is null
or is not null?

field: commission (currency)
the criteria is null works just fine in the query design, but not in the
parameter.

Thanks
Sammie
Click to expand...
Click to expand...
 
Sorry, Jamie, but I'm not familiar with SQL. I don't think I want to pursue
this. Thanks anyway.
Sammie
 
Sorry, Jamie, but I'm not familiar with SQL. I don't think I want to pursue
this. Thanks anyway.
Click to expand...

_You_ are not familiar with SQL, and don't want to be, yet you want to
expose the concept of NULL to the end user?! I wish you the best.

Jamie.

--
 
Back
Top