Date Range - Between Start and End

  • Thread starter Thread starter UnknownJoe
  • Start date Start date
U

UnknownJoe

Hello,
I have a table of employee information, which include 2 fields called Start
Date and End Date.

I want to be able to run a query that enables the user to enter a date range
to find those who are between a specific Start Date and End Date.

Example: Find those between Sept 1, 2008 (Start Date) and September 30, 2008
(End Date).

Thanks,
JL
 
You need to be a bit clearer on what you want. I am guessing that you want to
match records where the period Start Date to End Date overlaps fully or
partially the days in the date range that is input.

WHERE FldStartDate <= [Enter End Date] and fldEndDate >= [Enter Start Date]

That should return all records where there is an OVERLAP of the event dates
and the range.

If you want to return records where the entire event occurs within the
specified date range

WHERE FldStartDate >= [Enter Start Date] and fldEndDate <= [Enter End Date]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Two periods [start, end] and [starting, ending] do not overlap at all if

start > ending OR end < starting

So they somehow overlap in the negative:

start <= ending AND end >= starting


in your case:

start <= #9/30/2009# AND end >= #9/1/2009#



Vanderghast, Access MVP
 
UnknownJoe said:
I have a table of employee information, which include 2 fields called Start
Date and End Date.

I want to be able to run a query that enables the user to enter a date range
to find those who are between a specific Start Date and End Date.

Example: Find those between Sept 1, 2008 (Start Date) and September 30, 2008
(End Date).
Click to expand...


If you want to find only those records that start after
SartDate AND end before EndDate the set the StartDate's
field's criteria to something like >= #specific start date#
Similarly set the EndDate field's criteria to <= #specific
end date#

OTOH, if you want to retrieve the records where the
StartDate EndDate range overlaps the specific date raange,
then add a calculated field to the query:
Expr1: StartDate <= #specific end date# And EndDate >=
#specific start date#
with the criteria True
 
In the criteria for the Start Date:
=[Enter the Start Date]
Click to expand...

In the criteria for the End Date:

< [Enter the Start Date] + 1

This assumes that those two fields are actually Date/Time data types. Also
you should enter both of them as Date/Time data type parameters. If this is
Access 2003 or older, go up to Queries, Parameter while in design mode. You
need to enter each parameter (the stuff between and including the [ ] square
brackets) in the dialog box and make them Date/Time.
 
Since dtmParamRangeStart <= dtmParamRangeEnd and since also
dtmDataRangeStart <= dtmDataRangeEnd, the complex criteria can be simplified
to just

dtmDataRangeStart <= dtmParamRangeEnd And _
dtmDataRangeEnd >= dtmParamRangeStart


Vanderghast, Access MVP


KenSheridan via AccessMonster.com said:
If you are trying to identify intersecting date ranges then the following
function will do it:

Public Function WithinDateRange(dtmParamRangeStart As Date, _
dtmParamRangeEnd As Date, _
dtmDataRangeStart As Date, _
dtmDataRangeEnd As Date) As Boolean

' Accepts: date at which parameter range starts
' date at which parameter range ends
' date at which data range starts
' date at which data range ends
' Returns: True if data range intersects with parameter range
' False if whole of data range outside parameter range

' return True if:
' (a) start date within parameter range, or
' (b) end date within parameter range, or
' (c) parameter range entirely within data range

WithinDateRange = _
(dtmDataRangeStart >= dtmParamRangeStart And _
dtmDataRangeStart <= dtmParamRangeEnd) _
Or (dtmDataRangeEnd >= dtmParamRangeStart And _
dtmDataRangeEnd <= dtmParamRangeEnd) _
Or (dtmDataRangeStart <= dtmParamRangeStart And _
dtmDataRangeEnd >= dtmParamRangeEnd)

End Function


Call it in a query like so:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM [Employees]
WHERE
WithinDateRange([Enter start date:], Enter end date:],
[Start Date], [End Date]);

Ken Sheridan
Stafford, England
Hello,
I have a table of employee information, which include 2 fields called
Start
Date and End Date.

I want to be able to run a query that enables the user to enter a date
range
to find those who are between a specific Start Date and End Date.

Example: Find those between Sept 1, 2008 (Start Date) and September 30,
2008
(End Date).

Thanks,
JL
Click to expand...
Click to expand...
 
Back
Top