Query on or before date

[noosalucy]

I have a table for files in our office. There is a field for date files are
received and a field for date files are completed.

I would like to make a parameter query to show files which were current at a
particular time period - i.e. files that were received on or before a certain
date (to be entered) AND files that were completed on or after a certain date
(to be entered) OR files that are not completed (completed field is null).

Thanks

 

[Allen Browne]

So you want to enter a period (end date and start date), and return only
those records that were entered in that period or where the date is blank.

In the criteria row under this date field in query design, enter:
(>= [StartDate] AND < ([EndDate]+1)) OR Is Null

To ensure Access treats these parameters as dates, declare them.
Click Parameters on the Query menu/ribbon.
Access opens a dialog. Enter 2 rows:
StartDate Date/Time
EndDate Date/Time

 

[John Spencer]

Allen's advice is good; however, I note that you are talking about TWO fields.
My understanding of what you want is slightly different from what I perceive
to be Allen's understanding.

Field: DateReceived
Criteria (line 1): <= [Enter Last Received Date]

Field: DateCompleted
Criteria (line 2): >= {Enter Completed After Date]
Criteria (Line 3): Is Null

That will return
records where date received is before the first date entered
PLUS records where date completed is after the specified date
PLUS records where date completed is Null (Blank)

As Allen noted it is often a wise idea to declare the parameters.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County