Auto fill a date field based on another field?

[jeffcityman]

Hello to all,
I'm needing help with setting up a field (or control) in a Form to automatically fill in a number based on a date field. For example, in my form I have a field labeled "TRANS DATE". Users enter a date (such as 7/5/2013). I have another field labeled "FISCAL YR". I would like for FISCAL YR to be auto-filled with the last two digits of the year, based on the date entered in TRANS DATE. For example, if the date 7/1/2012 is entered in the TRANS DATE field, then I would want 13 to be auto-filled in the FISCAL YR field.
I've tried the After Update in an Event Procedure but haven't been successful yet. Below is one of the options I've tried:


If Me.TRANS_DATE = "Between 7/1/2012 and 6/30/2013" Then
Me.FISCAL_YR = 13
If Me.TRANS_DATE = "Between 7/1/2013 and 6/30/2014" Then
Me.FISCAL_YR = 14
If Me.TRANS_DATE = "Between 7/1/2014 and 6/30/2015" Then
Me.FISCAL_YR = 15

I've tried the # sign before and after the dates as well, with no luck.
I'm not sure if I'm doing the End If or End Sub part of it correctly either.
Thanks for listening!

 

[Beetle]

No reason to store this in a table field, just calculate it from the existing data in the TRANS_DATE field. You can do that easily using an expression like the following in a calculated query field or a calculated control on a form or report;

In a query fIeld:

FiscalYear: Right(Year([TRANS_DATE]), 2)

In the Control Source of a calculated form or report control:

=Right(Year([TRANS_DATE]), 2)

 

[Isskint]

Beetle's suggestion would give you the current year of the date but not necessarily the Financial Year. From your code, your FY is from 1 July to 30June so 31/12/2012 would give you 12 and 01/01/2013 would give you 13 but they are the same FY. Beetle is right though about not storing the value. It can be calculated everytime it needs to be shown (forms, queries, reports etc)

The answer (based on your approach) is, I think, DateValue() function.
If [SD] >= DateValue("01/07/2012") And [SD] <= DateValue("30/06/2013") Then Me.FISCAL_YR = 13

However, you would have to write code covering every year form now to ???? who knows. The better answer would be either a table with Start and End dates for FYs that you can use dlookup() on OR a custom function to calculate the FY, something like

Function FinYr(SupDate As Date) As Integer
Select Case Month(SupDate)
Case Is < 7
FinYr = Val(Right(SupDate, 2))
Case Else
FinYr = Val(Right(SupDate, 2)) + 1
End Select
End Function

In a query fIeld:

FiscalYear: FinYr([TRANS_DATE])

In the Control Source of a calculated form or report control:

=FinYr([TRANS_DATE])

 

[Beetle]

Beetle's suggestion would give you the current year of the date but not necessarily the Financial Year.

Good point. I hadn't really paid attention to the FY date ranges.