Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

2/22/2013 5:01:53 PM
Access Fiscal Year Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Well, I wasn't planning on making this a seminar, but at least three different customers asked me about this over the past two weeks, and one of them really needed to learn how to do it, and he submitted it as a PAID TechHelp question. So... I decided to turn it into a short seminar.

This Fiscal Year Seminar will teach you how to perform calculations in your Access databases based on a company's fiscal year. You will...

1. Learn how to calculate a simple fiscal year (starting 6/1, for example)
2. Create sales totals with an aggregate query or report grouping
3. Calculate fiscal year not based on the 1st of a month
4. Determine fiscal year for multiple companies in the same database

You can watch the free introductory and review lessons, and learn more about this seminar here: Microsoft Access Fiscal Year Seminar

Permanent Link
Course Link: Access Fiscal Year Seminar
Keywords: access fiscal year seminar
Page Tag: whatsnew
Post Reply

When your fiscal year starts Comment from Bruce Reynolds @ 3/21/2013
The VBA function as defined in 06:26 of this video may not be taking the valid approach. I think the function should be redefined as follows:

Public Function MyFiscalYear(DateIn As Date) As Integer

    MyFiscalYear = Year(DateIn)
    If Month(DateIn) > 5 Then
       MyFiscalYear = MyFiscalYear + 1
    End If

End Function

This way, if the fiscal year BEGINS on June 1st, 2012, then you are STARTING fiscal year 2013.

Reply from Richard Rost:

Bruce, that is a perfectly valid argument. It all depends on your needs and when your fiscal year starts. I've had clients who had their fiscal year 2012 run from 6/1/11 to 5/31/12, and I've had others that defined FY 2012 as 6/1/12 to 5/31/13. It's all up to you and how you want to define it for your business.

The Federal Government, for example uses a FY that ENDS in the current year, so FY 2013 is from October 1 2012 through September 30 2013. It runs from October 1 of the PRIOR year through Sept 30 of the current year.
Show Just This Thread        Post Reply
This VBA function works Comment from Bruce Reynolds @ 3/21/2013
This VBA function works EXACTLY the same way in Excel. I got two for the price of one!!!

Reply from Richard Rost:

You mean the PMT function? Yeah, it's the same function. Excel does have some additional financial functions that Access doesn't have (for the real financial nerds) but the important ones are included in Access.

Oops... ignore my last comment. I've been working on another seminar dealing with loan amortizations and I was thinking that you were talking about the PMT function... not this FY function we created in class... and YES, it works in Excel too. :)
Show Just This Thread        Post Reply
search in list box Comment from soorajsen foolell @ 3/4/2013
what would happen if an accountant has only 2 clients but with different fiscal years and he has to search a company by name in a list box.

Reply from Richard Rost:

Searching for a client using a COMBO box is a whole different tutorial. I cover that in my Access Beginner 8 class. If you want even MORE options for searching, take a look at my SEARCH SEMINAR.
Show Just This Thread        Post Reply
Fiscal year should have begin with opening day and Comment from soorajsen foolell @ 3/4/2013
Fiscal year should have begin with opening day and closing day .
Like #1/1/2012# (opening day) and #31/12/12# ( closing day)  also.

How would you navigate  the opening and closing day with Date Time Picker.

Reply from Richard Rost:

Well, you wouldn't need to specify the closing day. You can infer it as one day less than the start of the next fiscal year. No need to store both values.
Show Just This Thread        Post Reply
Better way to design table Comment from Soorajsen Foolell @ 3/4/2013

OrderDate DateTime NOT NULL,
Here i have created an order table with query and tested with access 2010 , it works!

Reply from Richard Rost:

Oh, sure. This is definitely possible. In fact, I cover this in part 3 of my SQL SEMINAR. However, I wouldn't say it's a "better" way to create a table. It's a good option if you need to create temporary tables, or want to allow the user to add a table on the fly without having to know anything about Access design. However, for the average Access user I would still recommend sticking with the good-old table designer.
Show Just This Thread        Post Reply
Comment from soorajsen foolell @ 3/4/2013
Each company name should be unique for each fiscal year.
Show Just This Thread        Post Reply
Better way to design table Comment from soorajsen foolell @ 3/3/2013
Better way to design table is with the use of query , it gives you more power and control over your table.

Reply from Richard Rost:

I don't understand what you mean. How do you design a table with the use of a query? Can you please explain in more detail?
Show Just This Thread        Post Reply
Fiscal Periods Comment from Jerry @ 2/26/2013
Perhaps you know it as 4-4-5_Calendar?
See the following reference:

Reply from Richard Rost:

Ah, yes. This is another perfectly valid method of calculating a fiscal year. It's not as commonly used as a simple fiscal year, but if enough people are interested in seeing this method, I can certainly put something together as an addendum to the seminar. It's a lot more difficult to calculate, but it's certainly doable.
Show Just This Thread        Post Reply
Fiscal Periods Comment from Jerry Parkinson @ 2/26/2013
Access Fiscal Year:
Why does this not calculate fiscal periods first and then calculate other fiscal data from that?

Reply from Richard Rost:

I don't understand the question. Can you elaborate?
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 1+1:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
6/30/2017Access Developer 2 is Finished
6/7/2017Microsoft Access Developer 1
6/6/2017Access Developer 1 is Finished
5/18/2017Microsoft Access Advanced 6
5/17/2017Access Advanced 6 is Online
4/9/2017Microsoft Access Advanced 5
4/9/2017Microsoft Access Advanced 4

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP