Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: Fiscal year dates

    I am still somewhat a novice in Access, and I'm trying to program a database that may very well be above my head. So, I post my next issue. I am attempting to get some sort of formula worked out so that it takes (on one form) the current date (now()), and calculates the beginning of the fiscal years. (October 1st), and also calculates the semester date, March 1. If anyone can help, it would be great. Thanks.

    Dave

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    why calculate, you know the dates, or i am missing something, do you want to calculate de days between ... ????

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    is this what your after

    Fiscal Year: IIf(Month([Date])>=10,Year([Date]) & "/" & Right(Year([Date])+1,2),Year([Date])-1 & "/" & Right(Year([Date]),2))

    Dave

  4. #4
    Join Date
    Oct 2004
    Posts
    7
    Ok... Here is the layout of the form...

    The first textbox is the current date. (NOW() )
    The second textbox is the fiscal year start date.
    The third textbox is the half year start date.

    When the database loads, I want it to take the current date, and find the fiscal year and half year start dates. 10/1 is the fy start date, and 10/1 and 3/1 are the half year start dates. I then want it to output those to the textboxes, named Fiscal_Year_Begin, and FY_Half_Begin. I am using these values to reference for training. The way it works is some training is necessary every fiscal year. Even if the training is completed 9/30, they have to do it again on 10/1. I want to reference those dates to query whether or not their training is current in this FY. Also, some training needs to be done every 1/2 year, and is referenced the same way. I appreciate the help, but it didn't seem to work... Keep in mind I'm not very familiar with VB, and have little experience in Access. Thanks...

    Dave

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Do you not just want to answer to find the year that it is and add 1 to it if it's for next year or minus 1 for the previous year something like

    =IIf(Month(Now())<10,CDate("10/01/" & Year(Now())),CDate("10/01/" & Year(Now())+1))

    into the control source of your text box this will fill your text box with that value or if your souce is a query look it up from that query

    all this does is looks for the month of the year if its less than ten choose this year else choose next year, play about with it and see what you want it to do

  6. #6
    Join Date
    Oct 2004
    Posts
    7
    =IIf(Month(Now())<10,CDate("10/01/" & Year(Now())-1),CDate("10/01/" & Year(Now())))

    That is the formula that I used. It works perfectly. Thanks a lot! Now, I have to play with it to calculate the semester dates... I'll try to figure it out... Thanks...

  7. #7
    Join Date
    Oct 2004
    Posts
    7
    Well, as I realize that I know very little to nothing about databases, a new problem arises. I would like to compare dates between fields in different forms. Their relationships are already set, but I can't get it to complete the process. I am working with scores, and I have the dates on them. I want it to read the date of the score, compare it to the FY date or FY 1/2 date when necessary, and if the date falls before that date, to check a box in the form called Needs_To_Requal_R. (Needs_To_Requal_R = True). So, I need to know how to define constants in the form as:

    1. The current date (Today)
    2. The computed FY date (FY)
    3. The computed FY half date (FYH)

    If someone could help out on this, it would be greatly appreciated.

Posting Permissions

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