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?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,178
Office Version
365
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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
54,178
Office Version
365
Platform
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
54,178
Office Version
365
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Good catch Joe...I hadn't noticed those quotes that way.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,547
Messages
5,511,977
Members
408,872
Latest member
Patrick Milner

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top