between ... and ...

[Ezekiël]

Hi,

I was wondering is it possible to get a resultset of records by using a
daterange? I know between ... and ... will give me results.

But how about if a date lies in the future and you want a range of the first
and last day of the current year to also include the future date.

e.g. : criteria is >= first day (01-01-2004), <= last day (31-12-2004)
some records: from 10-01-2004 to 30-09-2004
from 01-01-2004 to 01-01-2010
from 01-12-1993 to 26-12-2004

in the results, all records must be included because all records occur in
the current year, but how can i set this in a query?

 

[Rick B]

Access would not care that one of these dates was in the future. If you
specify "between 01/01and 12/31" then it would pull all dates in that range.
Where does the current date come into play?

Rick B

 

[Dale Fye]

Zeke,

Be extremely careful how you query on dates. The example you give below:

e.g. : criteria is >= first day (01-01-2004), <= last day (31-12-2004)

Will actually only get you records for the dates 1/1/2004 through 31-12-2004
12AM, which means if your date field contains time values, any records
entered on 31-12-2004 will be omitted from your query.

I think what you want is to use something like:

SELECT * FROM yourTable
WHERE yourDateField
BETWEEN DateSerial(YEAR(Date), 1, 1)
AND DateSerial(Year(date) + 1, 1, 1)

 

[Samuel]

Hi Dale,

That is almost what i'm looking for. But what i was wondering about, is how
to get also the ranges like 01-01-93 to 26-12-2006 because it occurs also in
the present time. Today's date is also in that range.

Greetings,

Zeke

 

[John Spencer (MVP)]

Ok, I think I understand what you are saying.
You have a table with two date fields. Field1 is the start date of an activity
and Field2 is the end date of the activity. You want to select those records
where the activity is still happening with in some specified date range
consisting of StartDateRange and EndDateRange.

Given that, I would use logic like the following

WHERE StartDateRange <= Field2 AND EndDateRange>=Field1