Concatenate Date

G

Guest

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!
 
M

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
 
J

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

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]
 
G

Guest

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]))
 
G

Guest

John,

This worked wonderfully. Thanks!

Leslie

John Spencer said:
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
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top