query input

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

Guest

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

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Back
Top