Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 format)

    The Kiwanis fiscal year is from October 1 through September 30.

    I want to be able to pull reports for the Current fiscal year (October 1, 2009 through September 30, 2010) and the Previous fiscal year (October 1, 2008 through September 30, 2010). And I want to feed those dates from the Main Menu to Start and End date text boxes on another form.

    Here is what I established for the Current fiscal year...
    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) + (Month(Date) < 9), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) + 1 - (Month(Date) > 10), 9, 30)
    And for the Previous fiscal year...
    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) - 1 + (Month(Date) < 9), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) - (Month(Date) > 10), 9, 30)
    I had slightly different VBA formulations of this which worked until October 1, 2009 rolled over.

    Will the VBA formulations I show provide correct results at all times?

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    They wouldn't work correctly for all months. For example, in November the end date of the current fiscal year would become September 30, 2011.

    Use these versions:

    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) + (Month(Date) < 10), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) + 1 + (Month(Date) < 10), 9, 30)
    And for the Previous fiscal year...
    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) - 1 + (Month(Date) < 10), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) + (Month(Date) < 10), 9, 30)
    Instead of (Month(Date) < 10) you could use (Month(Date) <= 9), this is equivalent.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797232' date='10-Oct-2009 12:56']They wouldn't work correctly for all months. For example, in November the end date of the current fiscal year would become September 30, 2011.

    Use these versions:

    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) + (Month(Date) < 10), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) + 1 + (Month(Date) < 10), 9, 30)
    And for the Previous fiscal year...
    Start Date
    Code:
    Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) - 1 + (Month(Date) < 10), 10, 1)
    End Date
    Code:
    Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) + (Month(Date) < 10), 9, 30)
    Instead of (Month(Date) < 10) you could use (Month(Date) <= 9), this is equivalent.[/quote]

    Thanks, Hans. Greatly appreciated.

    Tom

Posting Permissions

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