Fiscal Year (1 Viewer)

[Tor_Fey]

Good Morning All;

I have some criteria in my query which shows a calendar year as follows:

Quarter1: IIf(DatePart("q",[tbl_tf_ben_interview]![TF_Ben_Date_Interview_under_Caution])=1 And Year([tbl_tf_ben_interview]![TF_Ben_Date_Interview_under_Caution])=2017,1,Null)

But what I need to do is show the fiscal year from my table; tbl_year_financial, from the attached image.

How can I change my query criteria to look at the table: tbl_year_financial; to pull back records for my fiscal years?

Your help is very much appreciated.

Kind Regards
Tor Fey

 

[CJ_London]

your requirement is not clear

what I need to do is show the fiscal year from my table

provide an example of what this looks like

How can I change my query criteria to look at the table: tbl_year_financial; to pull back records for my fiscal years

what is your current criteria? again provide an example

 

[Tor_Fey]

Hi CJ_London;

Thank you for your reply, currently my query has only the following code for each quarter:

Quarter1: IIf(DatePart("q",[tbl_tf_ben_interview]![TF_Ben_Date_Interview_under_Caution])=1 And Year([tbl_tf_ben_interview]![TF_Ben_Date_Interview_under_Caution])=2017,1,Null)

How ever, i would like to change this code to look at a new table called: tbl_year_financial.

otherwise i have to write a query every year to look at my years.

the current query code looks at calendar year only for 2017, i need to be able to design a query that will pull the fiscal year in to the query based on the parameters on table: tbl_year_financial. which contains the following data:

year_financial: 17/18 - fin_year_start: 01/04/2017 - fin_year_end: 31/03/2017.

I have attached a screenshot of my query.

Kind Regards
Tor Fey

 

[CJ_London]

examples?

Perhaps someone else can pick this up - I am now out for the rest of the day

 

[Tor_Fey]

Hi CJ_London;

I have sorted it my self, went another way around it:

fyear: Right(Year([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution])-IIf([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution]<=DateSerial(Year([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution]),3,31),1,0),2) & "/" & Right(((Year([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution])-IIf([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution]<=DateSerial(Year([TBL_TF_Ben_Interview]![TF_Ben_Date_Interview_under_Caution]),3,31),1,0))+1),2)

Regards
Tor Fey

 

[arnelgp]

Does tbl_financial_year table will everytime have one record? If not can you add auto number field (ID)

Edit: i was making a function to include your financial table, and make use of it in the query since the start and end date doest not sync with normal Calendar year.

 

[Tor_Fey]

Hi arnelgp;

The table: tbl_financial_year has 3 records in it currently:

Year_financial fin_year_start fin_year_end
17/18 01/04/2017 31/03/2017
18/19 01/04/2017 31/03/2017
19/20 01/04/2017 31/03/2017

But would be added to after the period 19/20

Kind Regards
Tor Fey

 

[arnelgp]

Must be typo error ciz you have same date. Anyeay what is your indicator to the current financial year.

 

[Tor_Fey]

Hi arnelgp;

It was a typo, should have been like attached screenshot.

I want the indicator to be between the start and finish date of the financial table.

Regards
Tor Fey

 

[jdraw]

Here's some fiscal year stuff from M$oft that may be useful.
Issue was fiscal year start Oct 16 YYYY --- end Oct 15 YYYY+1

Option Compare Database
Option Explicit

'See http://support.microsoft.com/kb/210249 for more info
'on Fiscal Dates from Microsoft

Const FMonthStart = 10   ' Numeric value representing the first month
                        ' of the fiscal year.
Const FDayStart = 16    ' Numeric value representing the first day of
                        ' the fiscal year.
Const FYearOffset = -1  ' 0 means the fiscal year starts in the
                        ' current calendar year.
                        ' -1 means the fiscal year starts in the
                        ' previous calendar year.
                        
Function GetFiscalYear(ByVal x As Variant)
10       If x < DateSerial(year(x), FMonthStart, FDayStart) Then
20          GetFiscalYear = year(x) - FYearOffset - 1
30       Else
40          GetFiscalYear = year(x) - FYearOffset
50       End If
End Function
                    
Function GetFiscalMonth(ByVal x As Variant)
         Dim m
10       m = Month(x) - FMonthStart + 1
20       If Day(x) < FDayStart Then m = m - 1
30       If m < 1 Then m = m + 12
40       GetFiscalMonth = m
End Function

 

[arnelgp]

Here take a look and run qryFinancual. Take a look at the module

 

[Tor_Fey]

Hi arnelgp;

Thanks so much for your help, I will have a look at the database you provided.

Kind Regards
Tor Fey

 

[Tor_Fey]

Hi Arnelgp;

Thank you so much for this; it was exactly what i was looking for and has now been added to my database.

Kind Regards
Tor Fey

Here take a look and run qryFinancual. Take a look at the module