Date Restrictions - End of the Year

[Leviathan]

I'm trying to create a report that restricts results to the current year. I'm
using the following critieria:

=FormatDateTime("1/1/" & Year(Date())) And <Date()

The problem is that these reports are run off of datamarts that are cut at
the end of each month. So the July 2007 reports are run in August 2007 (the
<Date() doesn't matter since the data is cut as 31st/30th/etc. each month is
only meant to restrict obviously erroneous data (i.e. a year of 2013)). That
being said, when I need to run the December 2007 reports in January 2008, the
date year is going to be wrong. Is there a statement I can use (i.e.
if/then/else) to account for this once a year issue without having to adjust
the criteria?

Thank you for your help in advance!

 

[John Spencer]

I would try using DateSerial Function

Between DateSerial(Year(Date()) + IIF(Month(Date()) = 1,-1,0), 1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If you run the report in July, that should give you records between Jan 1,
2007 and June 30, 2007
If you run the report in January 2008, that should give you records from Jan
1, 2007 to Dec 31, 2007

Test it

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I'm trying to create a report that restricts results to the current year. I'm
using the following critieria:


The problem is that these reports are run off of datamarts that are cut at
the end of each month. So the July 2007 reports are run in August 2007 (the
<Date() doesn't matter since the data is cut as 31st/30th/etc. each month is
only meant to restrict obviously erroneous data (i.e. a year of 2013)). That
being said, when I need to run the December 2007 reports in January 2008, the
date year is going to be wrong. Is there a statement I can use (i.e.
if/then/else) to account for this once a year issue without having to adjust
the criteria?

Thank you for your help in advance!

I would not use FormatDateTime as a criterion... there are better
alternatives!

In order for any report run in January to retrieve data for the previous year,
and any report run in any other month to retrieve the current year, use

= DateSerial(Year(DateAdd("m", -1, Date())), 1, 1) AND < Date()

John W. Vinson [MVP]

 

[Leviathan]

Thanks for your help!

 

[Jamie Collins]

I would try using DateSerial Function

Between DateSerial(Year(Date()) + IIF(Month(Date()) = 1,-1,0), 1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If you run the report in July, that should give you records between Jan 1,
2007 and June 30, 2007
If you run the report in January 2008, that should give you records from Jan
1, 2007 to Dec 31, 2007

Test it

You seem to have forgetten that Access/Jet has but one temporal data
type named DATETIME and that the BETWEEN construct is inclusive of
both values. Here's one way to find the first time granule of the
current year and the (true) last time granule of the current month:

SELECT DATEADD('YYYY', DATEDIFF('YYYY', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 00:00:00#) AS current_year_start_date,
DATEADD('M', DATEDIFF('M', #1990-01-01 00:00:00#, NOW()), #1990-01-31
23:59:59#) AS current_month_end_date

Jamie.

--