Prev: Module "CarryOver" from Mr. Allen Browne
Next: Using Access query data lookup values
From: noosalucy on 28 Mar 2010 21:16
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

From: Allen Browne on 28 Mar 2010 21:26
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

--
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.


"noosalucy" <noosalucy(a)discussions.microsoft.com> wrote in message
news:4F2E41BE-8C22-478F-9B64-18CC7A706115(a)microsoft.com...
> 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).

From: John Spencer on 29 Mar 2010 09:04
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

Allen Browne wrote:
> 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
>
 | 
Pages: 1
Prev: Module "CarryOver" from Mr. Allen Browne
Next: Using Access query data lookup values