Trying to write a formula for a default value for a "Year" control on a form.
DistrictRecharterMonth is a numeric (ex. 6 = Jun); and CouncilName is alpha; both in the table tCouncilDefaults. I want the default to show the previous year up to one month before the recharter month.
For example if the recharter month is 6, then the default would be 2013 up until May 2014.
I've tried many variations of the following with/without "", , etc.
I suspect I'm using the wrong syntax somewhere.
Many more experiments in the immediate window yielded the following that worked:
year(dateadd("m",-DLookUp("DistrictRecharterMonth", "qCouncilDefaults", ("[CouncilName]like 'Great Lake*'"))-1,now()))