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