query input

M

Miree

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

Ofer Cohen

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)
 
M

Miree

That works with one but i am tring to run multiple searches all in different
feilds, inputing min and max for each feild, but some times the feilds are
blank i also need these results included into the search

Ofer Cohen said:
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)

--
Good Luck
BS"D


Miree said:
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.
Click to expand...
Click to expand...
 
J

Jamie Collins

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.
Click to expand...

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)
Click to expand...

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.

--
 
Top