Solved How to Create a Calculated Field for Fund Year based on 2 date fields? (1 Viewer)

abette

Member
Local time
Yesterday, 19:48
Joined
Feb 27, 2021
Messages
85
Good Morning,
I need to create a calculated field called 'Fund Year' that will be based on 2 existing fields, 'Date of Service From' and 'Data of Service To' values. So I am thinking something like this, "if D.O.S from Year < Current Year and D.O.S to Year < Current Year then Fund Year = Current Year – 1 else Fund Year = Current Year". I would like to add this logic in a query I have that imports data to examine the Date of Service fields and populate the Fund Year based on the algorithm described. Can someone help me convert this to query language? Of is this something that merits VBA? A wonderful person, Pat Hartman, helped me with another issues and was able to provide me a solution using a calc field in a query as opposed to having to code VBA. I appreciate any assistance/feedback.
Regards,
Ann Marie
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
14,556
SELECT *, IIF(Year([Date Of Service From]) < Year( Date()) And Year([Date Of Service To]) < Year(Date()), Year(Date())-1, Year(Date())) As [Fund Year] FROM yourTableName
 

abette

Member
Local time
Yesterday, 19:48
Joined
Feb 27, 2021
Messages
85
Thank you so much! It worked!!!!
 

Users who are viewing this thread

Top Bottom