I need a query that will take the input min and max and find the records
inbetween, but also if no min valued entered then all results bellow max and
if no max value all above the min.
Use the between with Nz function to replace Null with numbers that you cant
have below or under
SELECT *
FROM TableName
WHERE MyNum Between nz([Select Min],-9999999) And Nz([Select Max],999999999)
I don't think that's correct: the max is not "below the max" and the
min is not "above the min". The BETWEEN construct is inclusive of both
values and would only apply if the OP had said "below or equal to the
max" etc. Also, your hard-coded limits appear based on assumptions
that may not be true now or in the future i.e. they pose an element of
risk. Consider this revision, which includes the OP's later
requirement to include the NULL value (ANSI-92 Query Mode SQL DDL
syntax):
CREATE PROCEDURE TestProc (
[Select Min] INTEGER = NULL,
[Select MAX] INTEGER = NULL
)
AS
SELECT MyNum
FROM TableName
WHERE (MyNum IS NULL OR (MyNum > IIF([Select Min] IS NULL, (SELECT
MIN(T1.MyNum) FROM TableName AS T1), [Select Min])
And MyNum < IIF([Select MAX] IS NULL, (SELECT MAX(T1.MyNum) FROM
TableName AS T1), [Select MAX])))
Jamie.
--