Extracting Month and Year For a Given Date

[Jeff Garrison]

I know I've done this before, but for the life of me, I can't remember
how...

I have a fiscal calendar in a table with the fields Month, Year,
MonthBeginDate, and MonthEndDate.

What I need to do is from within a query, be able to get the Year and Month
for the day that is in another query, i.e. let's say that in one query I
have a date of 3/10/07. That date should come back from the Fiscal Calendar
table as being in Month 2 in Year 2007. (The fiscal calendar is offset by 1
month, meaning Feb is Period 1, Mar is Period 2, etc. with Jan being Period
12). I don't want to hard code the dates in the query's criteria for the
simple fact of at the beginning of the next fiscal year, the query criteria
would be wrong.

Do I run this with a query, or do I do it as a lookup?

Thanks

Jeff G

 

[Guest]

If your fiscal year is always offset by exactly one month then do not use a
table but the following for fiscal month and year.
Fiscal Month: Format(DateAdd("m",-1,[YourDateField]),"m")
Fiscal Year: Format(DateAdd("m",-1,[YourDateField]),"yyyy")

 

[Charles Wang[MSFT]]

Hi Jeff,
I understand that you would like to get the Fiscal Calendar year and month
from any normal date. The convertion rules is if the current month is 1,
the fiscal calendar month is 12 and the fiscal calendar year is (current
year-1).
If I have misunderstood, please let me know.


Hope the following statement can help you:
SELECT IIf(DatePart('m',[CurrentDate])=1,12,DatePart('m',[CurrentDate])) AS
[month],
IIf(DatePart('m',[CurrentDate])=1,DatePart('yyyy',[CurrentDate])-1,DatePart(
'yyyy',[CurrentDate])) AS [year]
FROM tblCurrent;

Please feel free to let me know if you have any other questions or
concerns. Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Jeff,

Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.

Have a great day!


Charles Wang
Microsoft Online Partner Support

PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!

Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================