Results 1 to 5 of 5

Fiscal Dates based on Calendar Date/Month

This is a discussion on Fiscal Dates based on Calendar Date/Month within the Microsoft Access forums, part of the Question Forums category; I need some fields in my form to auto populate with the current Fiscal Month and Week based on a ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Fiscal Dates based on Calendar Date/Month

    I need some fields in my form to auto populate with the current Fiscal Month and Week based on a table.

    In Excel I created this very easy by doing a vlookup on a table I built based on WeekNum and matching those weeks up with the fiscal months and weeks.

    How can I do this in access and have it do it automatically when someone enters a new record based on the Created Date.

    Thanks,

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,181

    Default Re: Fiscal Dates based on Calendar Date/Month

    Same basic idea. Build a time lookup table (typically this gets called AllTime) with a RefDate field containing a date sequence, and other fields for FiscalDate, FiscalMonth, FiscalWeek, and anything else you might need.

    In the form's record source, hook the AllTime table up to your CreateDate field (using RefDate). You can now place fields from the AllTime table onto the form. There should be no need to rewrite the data into the form's underlying table.

    Two things to watch:
    You're probably best off adjusting the join between the form's table and AllTime to display all records from
    and only those records from AllTime with matching data.
    Set the AllTime fields so that users can't edit them (in the properties of the control). You don't want people messing with the table; it's purely a lookup.

    Denis
    Last edited by SydneyGeek; Jul 19th, 2009 at 09:55 PM.
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Fiscal Dates based on Calendar Date/Month

    SydneyGeek thanks again... I have not performed any formulas in Access before.

    Can you give me a brief explaination on where I put these and an example?

    So if I am reading your post correctly I need the following

    1 table with 4-5 columns
    Fiscal Month
    Fiscal Week
    Calendar Month
    Calendar Week (like Excels weeknum)

    These would all align with each other.

    Then what!! Thanks again you have been a great help.

  4. #4
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,181

    Default Re: Fiscal Dates based on Calendar Date/Month

    Quote Originally Posted by PCRIDE View Post
    SydneyGeek thanks again... I have not performed any formulas in Access before.

    Can you give me a brief explaination on where I put these and an example?

    So if I am reading your post correctly I need the following

    1 table with 4-5 columns
    Fiscal Month
    Fiscal Week
    Calendar Month
    Calendar Week (like Excels weeknum)

    These would all align with each other.

    Then what!! Thanks again you have been a great help.
    Sorry for the delay but I have now tried four times to post to this thread and I keep getting bounced.

    If you are more comfortable in Excel create the table there. Then...

    1. Rename the worksheet tab to match the name of the table you want to create
    2. Copy the data and paste into the Tables section of Access. When prompted about column headers, click Yes.

    Once the table is in, build the query as I described before.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  5. #5
    MrExcel MVP CT Witter's Avatar
    Join Date
    Jul 2002
    Location
    Columbus, OH
    Posts
    1,208

    Default Re: Fiscal Dates based on Calendar Date/Month

    You may also want to check out the MS KB Article:

    HOW TO: Get the Fiscal Year or Month of a Particular Date in Access 2000
    http://support.microsoft.com/kb/210249

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com