How populate form with two dates representing fiscal year

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.

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?

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

Select allOpen in new window

29067527.accdb