Access Form, help in using data field to auto populate another field

[Ella359]

I have a Form I've created for data entry to track events. One of the fields is "Date of Event" and another entry field is "Fiscal Year". I'd like for the "Fiscal Year" field to be auto populated depending on the response given in "Date of Event". Basically I want it to work off of a range - 10/1/2014-9/31/2015. So if an event occurs some where in this range the "Fiscal Year" field auto populates to the corresponding FY 15.

Assistance would be greatly appreciated!

 

[MajP]

If you add a quarter to a given date, it will move that date into the correct fiscal year or keep it in the correct fiscal year.
example

=dateAdd("q",1,[yourDateField)

10/2/2014 would be 1/2/2015 moving to 2015
9/31/2015 would be 12/31/2015 staying in 2015

Now you can format this date only to show Year

=Format(dateAdd("q",1,[yourDateField),"F/Y yyyy")

This format string "F/Y yyyy" should produce the format FY 2015
So you normally would not store this value but calculate it in a query or calculated field. That way if you change your date the correct fiscal year would automatically update.


So in a query

Select [yourDateField,Format(dateAdd("q",1,[yourDateField),"F/Y yyyy") As FiscalYear,...

 

[Ella359]

If I add the quarter to the date is it going to change the date displayed? One field displays the event date and another field, the field I'm trying to get auto-populated, would display the fiscal year.

For example an event date would be 10/15/14 and the Fiscal Year would be 2015.

so if I had the quarter as suggest...will that make the date appear as 1/15/15 instead of 10/15/14???

 

[MajP]

You not adding a value to your date field. In an ubound calculated field or in a query you calculate and display.
So you could have field on a form showing your date and next to it a calculated control showing the fiscal year. Or you can do the same thing in a query.
You need to read up on calculated controls, and doing calculations in a query.

 

[Ella359]

Thank you very much for your help!