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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.