Concatenate Date

[Leslie W.]

Good Afternoon!

I have a table that has two fields representing the date as follows:

ReportingMonth (numeric 1-12)
FiscalYear (20072008 for FY 2008)

I would like to concatenate this, which seems simple enough, but I would
like to somehow translate this into the calendar date in a query (month and
year). Our fiscal year runs from July to June, so if it's months 7-12, then
it would be 2007, and 1-6 year 2008. I have information over multiple fiscal
years.

Any help would be appreciated. Thanks!

 

[Michel Walsh]

DateSerial( 2007 - Int( theMonth <= 6 ), theMonth, 1 )


which is based on the fact that for the result of a comparison: True is -1
and False is 0. So, if theMonth <=6, that evaluates to -1 and 2007 - -1
== 2008.


You can use an iif to be more explicit:

DateSerial( iif( theMonth<=6, 2008, 2007), theMonth, 1)


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Perhaps you can use the following expression.

DateSerial(IIF(ReportingMonth>6,Left(FiscalYear,4),Right(FiscalYear,4)),ReportingMonth,1)


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

 

[John W. Vinson]

Good Afternoon!

I have a table that has two fields representing the date as follows:

ReportingMonth (numeric 1-12)
FiscalYear (20072008 for FY 2008)

I would like to concatenate this, which seems simple enough, but I would
like to somehow translate this into the calendar date in a query (month and
year). Our fiscal year runs from July to June, so if it's months 7-12, then
it would be 2007, and 1-6 year 2008. I have information over multiple fiscal
years.

Any help would be appreciated. Thanks!

I'd suggest:

DateSerial(Left([FiscalYear], 4), 6 + [ReportingMonth], 1)

This will extract the start of the fiscal year, and ReportingMonth 1 will be
July of that year; ReportingMonth 12 will be June of the next year.

John W. Vinson [MVP]

 

[KARL DEWEY]

I recommend you change your field to a DateTime datatype and use true
reporting month in date format.
Then use the following to extract fiscal year.
Fiscal_Year: DatePart("yyyy",DateAdd("m",+6,[YourDate]))

 

[Leslie W.]

John,

This worked wonderfully. Thanks!

Leslie

Perhaps you can use the following expression.

DateSerial(IIF(ReportingMonth>6,Left(FiscalYear,4),Right(FiscalYear,4)),ReportingMonth,1)


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