How populate form with two dates representing fiscal year

If the current fiscal year is 7/1/2017 through 6/30/2018 how can I populate the two dates into two form fields?  And then when the current date is 7/1/2018 how do I populate the two date fields in the form with 7/1/2018 and 6/30/2019
SteveL13Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You should have some generic functions to handle this - see below. The you can use these expressions as ControlSource for your textboxes:

Primo: 
    =DateFinancialFirst()
Ultimo: 
    =DateFinancialLast()

Open in new window

And the functions:

Public Function DateFinancialFirst(Optional ByVal datNow As Date) As Date
  
  Dim datFin    As Date
  Dim datFirst  As Date

  If datNow = #12:00:00 AM# Then
    datNow = Date
  End If
  datFin = DateFinancial(datNow)
  datFirst = DateSerial(Year(datFin), 1 - DateDiff("m", datNow, datFin), 1)
 
  DateFinancialFirst = datFirst

End Function


Public Function DateFinancialLast(Optional ByVal datNow As Date) As Date
  
  Dim datFin    As Date
  Dim datLast   As Date

  If datNow = #12:00:00 AM# Then
    datNow = Date
  End If
  datFin = DateFinancial(datNow)
  datLast = DateSerial(Year(datFin) + 1, 1 - DateDiff("m", datNow, datFin), 0)
 
  DateFinancialLast = datLast

End Function


Public Function DateFinancial( _
  ByVal datDate As Date) _
  As Date
  
  ' Number of months from start of calendar year to start of financial year.
  Const clngMonthOffset As Long = 6
  
  Dim datFinancial      As Date
  
  datFinancial = DateAdd("m", -clngMonthOffset, datDate)
  
  DateFinancial = datFinancial
  
End Function

Open in new window

/gustav
0
 
Dale FyeCommented:
I usually use a table (tbl_App_Properties) in most of my applications, and instead of storing the fy start and end dates, I store a field FYOffset in that field, which would be, in your case, either 6 or -6, depending on whether 7/1/17 - 6/30/18 is FY 17 or 18.

Then, to determine the FY associated with a date, you would simply use the DateAdd function like:

FY: Year(DateAdd("m", 6, [DateField]))
or
FY: Year(DateAdd("m", -6, [DateField]))

to get the Fiscal year that the [DateField] is in.
0
 
SteveL13Author Commented:
Sorry.  Not following.  I setup a table named tbl_App_Properties.  how many field do I have in it and what are they named and what field type are they?

And then in the form fields, what do I use?
0
 
Ryan ChongCommented:
If the current fiscal year is 7/1/2017 through 6/30/2018 how can I populate the two dates into two form fields?  And then when the current date is 7/1/2018 how do I populate the two date fields in the form with 7/1/2018 and 6/30/2019
i believe in normal scenario the fiscal year is always fixed (in your case from 1 jul to 30 jun). Having said that, to better track it in your database, you may simply consider to store the exact fiscal year start date and end date in a table. And when compare with your date, just retrieve the associated fiscal year start date and end date.

i would have a table called as tbl_App_FY_Properties.

tbl_App_FY_Properties
ID
YR_StartDate
YR_EndDate

to populate the values of associated fiscal year start date and end date, you can use DLookup function:

Private Sub YourDate_AfterUpdate()
    If IsDate(YourDate.Value) Then
        txtFY_StartDate.Value = DLookup("FY_Startdate", "tbl_App_FY_Properties", "#" & Format(YourDate.Value, "mm/dd/yyyy") & "# between FY_Startdate and FY_Enddate ")
        txtFY_EndDate.Value = DLookup("FY_EndDate", "tbl_App_FY_Properties", "#" & Format(YourDate.Value, "mm/dd/yyyy") & "# between FY_Startdate and FY_Enddate ")
    End If
End Sub

Open in new window

29067527.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.