DatePart in IIF statement

jdrubins

New Member
Joined
Mar 13, 2009
Messages
3
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the Board!

You seem to be missing a double quote before the "yyyy" in the last part of your formula.
 
Upvote 0
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]))
 
Upvote 0
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]))
 
Upvote 0
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.
 
Upvote 0
Must be one of those quirks of Access... your revised expression worked like a charm so I'm just running with that. Thanks!!
 
Upvote 0
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!
 
Upvote 0
Good catch Joe...I hadn't noticed those quotes that way.
 
Upvote 0

Forum statistics

Threads
1,222,118
Messages
6,164,066
Members
451,870
Latest member
Nikhil excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top