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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

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

jdrubins

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 13, 2009
Messages
3
Must be one of those quirks of Access... your revised expression worked like a charm so I'm just running with that. Thanks!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Good catch Joe...I hadn't noticed those quotes that way.
 

Forum statistics

Threads
1,136,800
Messages
5,677,809
Members
419,721
Latest member
StuckInWork

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
Top