Re: Null values in date range, multiple criteria

Using:
<> "Closed"
won't handle nulls.
You would need:
WHERE (([Forms]![FrmSelect]![CmbStatus]="open")
OR ([Forms]![FrmSelect]![CmbStatus] Is Null)
OR (MyDate Between ...

And, yes, Access does make a mess of the SQL with combinations of AND and
OR. The bracketing is really crucial when you mix AND and OR. The query
design grid isn't really flexible enough, so it ends up repeating whole
tracts of it.

If you have several of these controls, and any could be Null, it might be
more efficient and flexible to leave the criteria out of the query, and
build a Filter string in code from only those boxes where the user entered
something. If that interests you, there's an example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark in Michigan" <MarkinMichigan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:B15BDADE-99BC-4376-B100-C60BF7745BC2@xxxxxxxxxxxxxxxx
Allen,

Thanks much - that worked! I just changed "=Open" to "<>Closed" to account
for null values in that control.

BTW, in design view, it added another row of criteria, duplicating all the
other fields and leaving "Closed Date" without criteria in the "or" row.
It
then added a column for the forms Status control, leaving that criteria
blank
in the first row and adding <>Closed as criteria in the "OR" row.

"Allen Browne" wrote:

Mark, switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause.
Try something like this, replacing "MyDate" with the name of your date
field:

WHERE (([Forms]![FrmSelect]![CmbStatus]="open")
OR ([MyDate] Between [Forms]![FrmSelect]![TxtCloseDate]
And [Forms]![FrmSelect]![txtCloseDate2]))

By way of explanation, a WHERE clause ultimately evalutes to True (select
the record) or False (reject the record.) The clause above consists of 2
parts. With the OR operator, it evaluates to True if either part is true.
So, if the combo contains "open" the clause will evaluate to True for all
records. If the combo does not contain "open", the WHERE clause will be
true
only if the other part is true, i.e. if the date is between the 2 values.

"Mark in Michigan" <Mark in Michigan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:9378B2B2-8775-4C5F-83BC-4FFEFA4FCF39@xxxxxxxxxxxxxxxx
I'm trying to create a criteria to do the following:

If the "status" control on a form = "open", no criteria (want to pick
up
null values).

else:

pick up items within a date range as specified in two other controls.

I'm trying:

IIf([Forms]![FrmSelect]![CmbStatus]="open",Is
Null,<=[Forms]![FrmSelect]![TxtCloseDate] And
=[Forms]![FrmSelect]![txtCloseDate2])

to no avail. Any help much appreciated


.

Relevant Pages

  • Re: Not Exists joining 2 tables
    ... "'code' is a bad name for a key column" is a valid complaint. ... EXISTS clause with a correlated subquery properly, ... SQL+ syntax and start using the ANSI SQL syntax that seems ... Then the only criteria in the where clause ...
    (comp.databases.ms-sqlserver)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)
  • Re: Access Group by and Count problem
    ... This criterion should be put in the WHERE clause, since it has nothing to do ... Criteria in the WHERE clause are enforced BEFORE ... GROUP BY PageVisited, StartTime ... in the grid, instead of one of the aggregation functions. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Select query populating list box
    ... Additional assumption is that txtInstitution is never null. ... Every time you add one more criteria to the where clause, ... However if you look at my original code the text control needs to search ...
    (microsoft.public.access.queries)
  • Re: VBA function break string input for SQL string
    ... It's intended the criteria be applied to a single ... Had 4 textboxes ... then some additional code built the Where clause. ... string built, it's just breaking the component words into ...
    (comp.databases.ms-access)