DatePart in IIF statement

[jdrubins]

I am trying to run a query that will generate a fiscal year based on a date in a table (called fin_month). If the month portion of the date selected is between 10 and 12, I need the year to be +1 (goverment fiscal year). If the month portion is between 1 and 9, then I just need the year. I've put the following string together that I think should work...

Fiscal_Year: iif(datepart("M", [fin_month]) >= 10, datepart("yyyy", [fin_month])+1, datepart(yyyy", [fin_month]))

But, when I click off of the field line in the query, Access is automatically changing the format of the statement to the following...

Fiscal_Year: IIf(DatePart(["M"],[fin_month])>=10,DatePart(["yyyy"],[fin_month])+1,DatePart(["yyyy"],[fin_month]))

Notice the [] being put around the portion of the datepart. This is causing the query to not run correctly. Can anyone suggest a solution to this or why Access is inserting these brackets?

 

[Joe4]

Welcome to the Board!

You seem to be missing a double quote before the "yyyy" in the last part of your formula.

 

[jdrubins]

Sorry, that was a typo in posting... the " on the last yyyy does exist in the string I'm trying, but still has the [] added.

Fiscal_Year: iif(datepart(“M”, [fin_month]) >= 10, datepart(“yyyy”, [fin_month])+1, datepart(“yyyy”, [fin_month]))

 

[xenou]

Your expression works for me and no brackets are added. Doesn't really make sense.

You can try instead:
Fiscal_Year: IIf(Month([fin_month])>=10,Year([fin_month])+1,Year([fin_month]))

 

[Joe4]

Access and SQL can be very picky.

Note that these quotes: “”
are NOT the same as these quotes: ""

When I copied your formula, I got the same errors you were getting. When I switched the quote marks, it worked fine.

 

[jdrubins]

Must be one of those quirks of Access... your revised expression worked like a charm so I'm just running with that. Thanks!!

 

[Joe4]

Access is really a simplified version of SQL, and SQL is also that picky about syntax. Actually when you get down to it, most computer programming languages are!

 

[xenou]

Good catch Joe...I hadn't noticed those quotes that way.