RE: Null or No Null values in a parameter query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Filter Sort Annoyance
    ... If your selection criteria change, ... use the same query and modify it by changing the selection criterion. ... I'll suggest that further normalization may be needed. ... In a well-normalized data design, ...
    (microsoft.public.access.tablesdbdesign)
  • 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: Dynamic Query Criteria and returning all records (*)
    ... You'll find that after saving the query in design view, ... and saving queries like this in SQL view. ... criteria prior to running a query or a report. ...
    (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: Show all records
    ... When I re-opened it in design view. ... and everything after the OR was turned into another field at the end of the columns in the query, with IS NULL in its criteria. ... When you switch to some other view and then back to design view, Access will have rearranged things in the design grid and may have made it harder to make subsequent adjustments correctly. ...
    (microsoft.public.access.forms)