IIF statement in query

[ScottXe]

I would like to extract the year part from a date field as a Fiscal Year field. I believe IIF statement can help in this case.

FiscalYear: IIf([Completed]>=#01/05/2014#,DatePart("yyyy",[Completed]),(DatePart("yyyy",[Completed]))-1)



How can I use only 01/05 without year for comparison? The completed field may contain different year dates. Thanks!

[JoeM]

Try using the Format function, i.e.
Format([DateField],"mm/dd")

[ScottXe]

Try using the Format function, i.e.
Format([DateField],"mm/dd")

Hi JoeM,
Thanks for your advice that works well. What is the need to have "/" between mm and dd? I tried to remove it. Without it, it will become 4 digit comparison - xxxx vs 0501. It looks more straight forward comparison, isn't it?

[rpeare]

FY: datepart("yyyy", [Completed]) + iif(datepart("m", [Completed]) >=5, 0,1)

this is assuming your fiscal year starts on May 1