Re: Null values in date range, multiple criteria

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"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: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... From the User Interface, in the toolbar, or the menu, when you edit a query, ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... you should find a button that allows you to change the query "type". ... If this is what you want, fine, else, add a WHERE clause to ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If you have some joins or WHERE clause in your statement, ... Also try to minimize selection of the records using WHERE ... Incase of actual action query, ... >> of queries and I've concluded that in case of an internet conection the ...
    (microsoft.public.vb.database.ado)