# Fiscal Year 10/1 - 9/30 • ### Question

• Hello everyone, Thank you all for the assistance. I hate to keep posting, but I did figure out how to calculate the Fiscal Year, I isolated the month and year and did an iff statement to get the fiscal year. Now I just want to be able to see the data from the current fiscal year only. Below is my SQL.

SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], CDate(Nz([Current Rifle Qual Dt],"0")) AS [Date], Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf([CalendarMonth]>=10,[CalendarYear]+1,[CalendarYear]) AS [Fiscal Year] FROM Training ORDER BY Training.[Current Rifle Qual Dt]; ﻿

LDW

Wednesday, September 2, 2015 4:15 PM

• Ok, try wich statment get error:

#1:

```SELECT top 1
Year(Cdate(Nz([current rifle qual dt], "0")))
+ Iif(Month(Cdate(Nz([current rifle qual dt], "0")))>=10, 1, 0)
FROM   training
```

#2

```SELECT top 1
Year(DATE()) + Iif(Month(DATE())>=10, 1, 0)
FROM   training ```

Michał

Wednesday, September 2, 2015 8:45 PM

### All replies

• ```SELECT DISTINCT
training.rank,
training.[last name],
training.[f name],
training.[current rifle qual class cd],
training.[current rifle qual dt],
training.[rifle exception code],
training.[dod id],
Cdate(Nz([current rifle qual dt],"0")) AS [Date],
Year([date])                           AS CalendarYear,
Month([date])                          AS CalendarMonth,
year([date]) + Iif([calendarmonth]>=10,1,0) AS [Fiscal Year]
FROM
training
WHERE
year([date]) + Iif([calendarmonth]>=10,1,0) = 2015
ORDER BY
training.[current rifle qual dt];﻿```

But I thinkt better way is add table fiscalYears:

fiscalYear, start_date,end_date,iscurrent

2015,10/01/2014,9/30/2015,1

2016,10/01/2016,9/30/2016,0

and in query:

```SELECT DISTINCT
t.rank,
t.[last name],
t.[f name],
t.[current rifle qual class cd],
t.[current rifle qual dt],
t.[rifle exception code],
t.[dod id],
Cdate(Nz(t.[current rifle qual dt],"0")) AS [Date],
t.fiscalYear AS [Fiscal Year]
FROM
training as t
inner join fiscalYears as y on Cdate(Nz(t.[current rifle qual dt],"0")) between y.start_date and y.end_date
WHERE
isCurrent=1
ORDER BY
t.[current rifle qual dt];﻿```

Michał

Wednesday, September 2, 2015 5:02 PM
• I am trying to have it automatically populate the current fiscal year without having to enter a date.  = 2015 I will have to modify it when I hit 10/1/2015 because that is the start of FY 2016.

LDW

Wednesday, September 2, 2015 5:27 PM
• Use Date() function:

```WHERE
(year([date]) + Iif([calendarmonth]>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0))```

Michał

Wednesday, September 2, 2015 6:06 PM
• Below is the sql based off your recommendation Michal, however, it didn't work. Did I do something wrong?

SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], CDate(Nz([Current Rifle Qual Dt],"0")) AS [Date], Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf([CalendarMonth]>=10,[CalendarYear]+1,[CalendarYear]) AS [Fiscal Year] FROM Training WHERE (year([date]) + Iif([calendarmonth]>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0)) ORDER BY Training.[Current Rifle Qual Dt];

LDW

Wednesday, September 2, 2015 6:36 PM
• You cannot use column aliases in where and select:

```SELECT DISTINCT
training.rank,
training.[last name],
training.[f name],
training.[current rifle qual class cd],
training.[current rifle qual dt],
training.[rifle exception code],
training.[dod id],
Cdate(Nz([current rifle qual dt],"0")) AS [Date],
Year(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarYear,
Month(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarMonth,
year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0) AS [Fiscal Year]
FROM
training
WHERE
﻿    (year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0))
ORDER BY
training.[current rifle qual dt];```

Michał

Wednesday, September 2, 2015 6:49 PM
• I am getting an undefined expression error.

SELECT DISTINCT Training.RANK, Training.[LAST NAME], Training.[F NAME], Training.[Current Rifle Qual Class Cd], Training.[Current Rifle Qual Dt], Training.[Rifle Exception Code], Training.[DOD ID], Cdate(Nz([current rifle qual dt],"0")) AS [Date], Year(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarYear, Month(Cdate(Nz([current rifle qual dt],"0"))) AS CalendarMonth, year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0) AS [Fiscal Year]

FROM training

WHERE ﻿ (year(Cdate(Nz([current rifle qual dt],"0"))) + Iif(Month(Cdate(Nz([current rifle qual dt],"0")))>=10,1,0)) = (year(Date()) + IIF(month(Date())>=10,1,0))

ORDER BY training.[current rifle qual dt];

LDW

Wednesday, September 2, 2015 7:42 PM
• LDW,

Where is the undefined expression?  try:

```SELECT DISTINCT
*
FROM   training
WHERE  ﻿ (Year(Cdate(Nz([current rifle qual dt], "0")))
+ Iif(Month(Cdate(Nz([current rifle qual dt], "0")))>=10, 1, 0)) = (
Year(DATE()) + Iif(Month(DATE())>=10, 1, 0) )
ORDER  BY training.[current rifle qual dt]; ```

Michał

Wednesday, September 2, 2015 8:05 PM
• I am still getting undefined expression.  If I take out the where statement it works

LDW

Wednesday, September 2, 2015 8:29 PM
• Ok, try wich statment get error:

#1:

```SELECT top 1
Year(Cdate(Nz([current rifle qual dt], "0")))
+ Iif(Month(Cdate(Nz([current rifle qual dt], "0")))>=10, 1, 0)
FROM   training
```

#2

```SELECT top 1
Year(DATE()) + Iif(Month(DATE())>=10, 1, 0)
FROM   training ```

Michał

Wednesday, September 2, 2015 8:45 PM