Null values in date range, multiple criteria

[Guest]

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

 

[Jeff L]

Criteria for what? A query? A form filter? Where are you attempting
to do this? A little more explanation is needed.

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

 

[Guest]

Try a little bit at a time. First use this as criteria and see if you have
any results.
Is Null

Then try --
<=[Forms]![FrmSelect]![TxtCloseDate] And

=[Forms]![FrmSelect]![txtCloseDate2])

Check to see what the control [Forms]![FrmSelect]![CmbStatus] is giving the
query by adding an output field that display the control.

Build a little, Test a little.

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

 

[Guest]

Sorry, the criteria is on a query.

As I've thought about this further, it's even more complicated than I
explained. I did not develop the form, trying to fix up someone else's work.

Each of the controls on the forms contains a check box to check if the
criteria is to be used. Then, in the query, each criteria uses an IIF
statement such as:

IIf([Forms]![FrmSelect]![ChkType]=True,[Forms]![FrmSelect]![CmbType],[Type])

to apply the criteria as needed.

This works fine for most items, but not for a date field that may contain
null values.

The field I'm working with is "Closed Date". This only has a value if the
file is closed, otherwise the field is blank. This field does not use the
check box approach, instead, we force default values onto the form to ensure
that all items will be included (the user can narrow the range). The problem
comes when the user wants open items (status="open") because the criteria in
the closed date field will not return null values:

<=[Forms]![FrmSelect]![TxtCloseDate] And

=[Forms]![FrmSelect]![txtCloseDate2]

So, if "CmbStatus" is anything but "Closed" (null or open), I want the
criteria on the query for the closed date to not filter out any records.

Criteria for what? A query? A form filter? Where are you attempting
to do this? A little more explanation is needed.

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

 

[Guest]

Thanks,

I've tried this approach and each of the steps along the way works fine,
it's when I try to combine everthing with the IIF statement that it doesn't
work. See additional information posted as a response to the other answer I
received.

Try a little bit at a time. First use this as criteria and see if you have
any results.
Is Null

Then try --
<=[Forms]![FrmSelect]![TxtCloseDate] And

=[Forms]![FrmSelect]![txtCloseDate2])

Check to see what the control [Forms]![FrmSelect]![CmbStatus] is giving the
query by adding an output field that display the control.

Build a little, Test a little.

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

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

 

[Guest]

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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

message

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.

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.

message

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