RE: Null or No Null values in a parameter query

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


"tim" wrote:

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?

"KARL DEWEY" wrote:

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


"tim" wrote:

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?

"KARL DEWEY" wrote:

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


"tim" wrote:

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

Relevant Pages

  • RE: Creating a query selecting multiple fields
    ... Your second post said 'I would like to place the criteria on the ID.' ... This query should do what you asked. ... "NeedExcelHelp07" wrote: ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.queries)
  • Re: Data type mismatch in a simple query
    ... Removing the criteria from Step 2 does provide identical results to Step ... without teaching me why my two step select query doesn't work. ... but doesn't separating the calculation query from the criteria query ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.queries)
  • RE: Null or No Null values in a parameter query
    ... I can see that a field called 1 was added to my query with a ... "KARL DEWEY" wrote: ... Look at it in design view. ... I prompt the user for the date criteria. ...
    (microsoft.public.access.gettingstarted)
  • RE: Query not returning all values
    ... "handsclark" wrote: ... "KARL DEWEY" wrote: ... in the design view grid of the query add a new field ... Apply you criteria to this field. ...
    (microsoft.public.access.queries)
  • RE: Close and move record
    ... Open your query in design view and double click on the Yes/No field name in ... For all records use no criteria. ... I didn't create queries in this form. ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.forms)