Null or No Null values in a parameter query

[tim]

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?

 

[KARL DEWEY]

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

 

[tim]

Thx very much Karl. I have programmed this and it works. I have a couple of
questions.

First, some of the "or" and "and" statements in your example are capitalized
and some are not - does this make a difference? Were you trying to emphasize
something? Seems like SQL made them all start with an uppercase, followed by
lower case letters.

Second, I don't quite follow the logic of what we are asking in the Where
statement that you wrote - we have:

(BLDG.BLDG) Between [Enter start] And [Enter end]

in the first part of the OR clause and then again as a second separate
statement in the second part of the OR clause. Why do we need it again as a
second statement - it seems like we already asked for it in the first part?

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

--
KARL DEWEY
Build a little - Test a little

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?

 

[KARL DEWEY]

in your example are capitalized and some are not - does this make a
difference?
No.

(BLDG.BLDG) Between [Enter start] And [Enter end] -- You would change BLDG
with your 'Due Dates' field and in this case input a range of dates for start
and end.

You need it a second time - first for nulls and second for no nulls.
Look at it in design view.
--
KARL DEWEY
Build a little - Test a little

Thx very much Karl. I have programmed this and it works. I have a couple of
questions.

First, some of the "or" and "and" statements in your example are capitalized
and some are not - does this make a difference? Were you trying to emphasize
something? Seems like SQL made them all start with an uppercase, followed by
lower case letters.

Second, I don't quite follow the logic of what we are asking in the Where
statement that you wrote - we have:

(BLDG.BLDG) Between [Enter start] And [Enter end]

in the first part of the OR clause and then again as a second separate
statement in the second part of the OR clause. Why do we need it again as a
second statement - it seems like we already asked for it in the first part?

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

--
KARL DEWEY
Build a little - Test a little

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?

 

[tim]

Karl,

Your (good) suggestion generated more questions - you may be sorry!
In design view, I can see that a field called 1 was added to my query with a
criteria evaluating the logical condition of whether my user-defined value is
null or not, then a value of 1 or 0 assigned depending on the condition. What
is this and how does it work? Can you explain a little more?

difference?
No.

(BLDG.BLDG) Between [Enter start] And [Enter end] -- You would change BLDG
with your 'Due Dates' field and in this case input a range of dates for start
and end.

You need it a second time - first for nulls and second for no nulls.
Look at it in design view.
--
KARL DEWEY
Build a little - Test a little

Thx very much Karl. I have programmed this and it works. I have a couple of
questions.

First, some of the "or" and "and" statements in your example are capitalized
and some are not - does this make a difference? Were you trying to emphasize
something? Seems like SQL made them all start with an uppercase, followed by
lower case letters.

Second, I don't quite follow the logic of what we are asking in the Where
statement that you wrote - we have:

(BLDG.BLDG) Between [Enter start] And [Enter end]

in the first part of the OR clause and then again as a second separate
statement in the second part of the OR clause. Why do we need it again as a
second statement - it seems like we already asked for it in the first part?

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

--
KARL DEWEY
Build a little - Test a little


:

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?

 

[KARL DEWEY]

IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0) test if
operator pressed ENTER or 'N' then enter for with or without nulls.

All criteria on the same line must be met -- they are AND'd together.

--
KARL DEWEY
Build a little - Test a little

Karl,

Your (good) suggestion generated more questions - you may be sorry!
In design view, I can see that a field called 1 was added to my query with a
criteria evaluating the logical condition of whether my user-defined value is
null or not, then a value of 1 or 0 assigned depending on the condition. What
is this and how does it work? Can you explain a little more?

in your example are capitalized and some are not - does this make a

difference?
No.

(BLDG.BLDG) Between [Enter start] And [Enter end] -- You would change BLDG
with your 'Due Dates' field and in this case input a range of dates for start
and end.

You need it a second time - first for nulls and second for no nulls.
Look at it in design view.
--
KARL DEWEY
Build a little - Test a little

Thx very much Karl. I have programmed this and it works. I have a couple of
questions.

First, some of the "or" and "and" statements in your example are capitalized
and some are not - does this make a difference? Were you trying to emphasize
something? Seems like SQL made them all start with an uppercase, followed by
lower case letters.

Second, I don't quite follow the logic of what we are asking in the Where
statement that you wrote - we have:

(BLDG.BLDG) Between [Enter start] And [Enter end]

in the first part of the OR clause and then again as a second separate
statement in the second part of the OR clause. Why do we need it again as a
second statement - it seems like we already asked for it in the first part?

:

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

--
KARL DEWEY
Build a little - Test a little


:

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?

 

[tim]

thx for you help and your patience Karl.

IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0) test if
operator pressed ENTER or 'N' then enter for with or without nulls.

All criteria on the same line must be met -- they are AND'd together.

--
KARL DEWEY
Build a little - Test a little

Karl,

Your (good) suggestion generated more questions - you may be sorry!
In design view, I can see that a field called 1 was added to my query with a
criteria evaluating the logical condition of whether my user-defined value is
null or not, then a value of 1 or 0 assigned depending on the condition. What
is this and how does it work? Can you explain a little more?

in your example are capitalized and some are not - does this make a
difference?
No.

(BLDG.BLDG) Between [Enter start] And [Enter end] -- You would change BLDG
with your 'Due Dates' field and in this case input a range of dates for start
and end.

You need it a second time - first for nulls and second for no nulls.
Look at it in design view.
--
KARL DEWEY
Build a little - Test a little


:

Thx very much Karl. I have programmed this and it works. I have a couple of
questions.

First, some of the "or" and "and" statements in your example are capitalized
and some are not - does this make a difference? Were you trying to emphasize
something? Seems like SQL made them all start with an uppercase, followed by
lower case letters.

Second, I don't quite follow the logic of what we are asking in the Where
statement that you wrote - we have:

(BLDG.BLDG) Between [Enter start] And [Enter end]

in the first part of the OR clause and then again as a second separate
statement in the second part of the OR clause. Why do we need it again as a
second statement - it seems like we already asked for it in the first part?

:

Substitute your table and field names ---
SELECT BLDG.BLDG, 1 AS X
FROM BLDG
WHERE (((BLDG.BLDG) Between [Enter start] And [Enter end] Or (BLDG.BLDG) Is
Null) AND ((1)=IIf([Press ENTER for nulls, 'N" not nulls] Is Null,1,0))) OR
(((BLDG.BLDG) Between [Enter start] And [Enter end]));

--
KARL DEWEY
Build a little - Test a little


:

I have a database of projects with fields that include Date_assigned and
Date_due. Not every project has a Date_due value now, some are null. I have
parameter query to view projects due by a user-defined period of dates, where
I prompt the user for the date criteria (meaning the period of interest).
Right now, I have the query criteria also include projects with Null values
(no Due Dates). Is it possible to make this a user-defined criteria too and
allow the user to include or exclude projects with no Due Date? If so, how
would I do this - would it be in the criteria as well?