Solved Format function to get Current financial year data from (April-20 to March-21) (1 Viewer)

hrdpgajjar

Registered User.
Local time
Tomorrow, 04:45
Joined
Sep 24, 2019
Messages
36
Hi all,
I need to get report of material sent by current week with a query. I am able to get report of current month by following funtion,

Format([Material Sent Date], "yyyymm") - by this function I am able to get current month data and,

Format([Material Sent Date], date()) - by this function I am able to get current date (todays date) data

but I need to get data of current financial year (i.e. from March to April) by the above function.

Please help how can I do that.

Or is there any other solution by which I can get the said data?


Thank you,
 

Minty

AWF VIP
Local time
Today, 23:15
Joined
Jul 26, 2013
Messages
8,900
The SQL Where clause in a query would be something like
Code:
Where DateValue([Material Sent Date]) >= #2020-04-01# AND DateValue([Material Sent Date]) <=#2021-03-31#

You can remove the DateValue() bit if your field doesn't have any time values stored.
I'm not sure how your formatting shown above is applying criteria?
 

hrdpgajjar

Registered User.
Local time
Tomorrow, 04:45
Joined
Sep 24, 2019
Messages
36
The SQL Where clause in a query would be something like
Code:
Where DateValue([Material Sent Date]) >= #2020-04-01# AND DateValue([Material Sent Date]) <=#2021-03-31#

You can remove the DateValue() bit if your field doesn't have any time values stored.
I'm not sure how your formatting shown above is applying criteria?
I need data of current financial year only. I mean after a year the value will be from April-21 to March-22 . is this working here? I am asking as you have entered fixed date #2020-04-01# and #2021-03-31#. Or I have to change year every time? Thanks for the prompt reply.
 

Minty

AWF VIP
Local time
Today, 23:15
Joined
Jul 26, 2013
Messages
8,900
Ah okay, so you want it to calculate automatically based on the current date, from April to March Next year?
 

Minty

AWF VIP
Local time
Today, 23:15
Joined
Jul 26, 2013
Messages
8,900
It's probably easiest to have two functions to return the current start and end dates than cram it into one line of a criteria expression.
I actually use a calendar table for a couple of financial-based databases, as it is easier and quicker on large datasets to use a join or look up the values, however, this will give you the start of the current FY

Code:
Function fCurrYearStart() As Date
           
    Dim iYear As Integer

    iYear = Year(Date)
               
    If Month(Date) < 4 Then iYear = iYear - 1
 
    fCurrYearStart = DateSerial(iYear, 4, 1)
   
   
End Function
I'm sure from that, you can work out how to create a similar function to give you the end of the current FY
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:15
Joined
May 7, 2009
Messages
14,555
create two function that will return the Starting date/ending date of financial year:
Code:
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
    If start_date = 1 Then
        start_date = DateSerial(Year(Date), 4, 1)
    End If
    If Date < start_date Then
        start_date = DateAdd("yyyy", -1, start_date)
    End If
    fnFinancialYearStart = start_date
End Function


Public Function fnFinancialYearEnd(Optional Byval start_date As Date = 1) As Date
    start_date = fnFinancialYearStart(start_date)
    fnFinancialYearEnd = DateAdd("yyyy", 1, start_date) - 1
End Function

on your query criteria:
Code:
Where DateValue([Material Sent Date]) Between fnFinancialYearStart() AND fnFinancialYearEnd()
 
Last edited:

hrdpgajjar

Registered User.
Local time
Tomorrow, 04:45
Joined
Sep 24, 2019
Messages
36
create two function that will return the Starting date/ending date of financial year:
Code:
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
    If start_date = 1 Then
        start_date = DateSerial(Year(Date), 4, 1)
    End If
    If Date < start_date Then
        start_date = DateAdd("yyyy", -1, start_date)
    End If
    fnFinancialYearStart = start_date
End Function


Public Function fnFinancialYearEnd(Optional Byval start_date As Date = 1) As Date
    start_date = fnFinancialYearStart(start_date)
    fnFinancialYearEnd = DateAdd("yyyy", 1, start_date) - 1
End Function

on your query criteria:
Code:
Where DateValue([Material Sent Date]) Between fnFinancialYearStart() AND fnFinancialYearEnd()
Well Well ! I am always learning something new from you. This works like a charm Thank you very much !!!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:15
Joined
May 7, 2009
Messages
14,555
unfortunately, the first function need revision (rev.1A), it is always Comparing to the Current date
when it should be comparing to April of the passed date.

Code:
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
    Dim x_date As Date
    If start_date = 1 Then
        start_date = Date
    End If
    x_date = DateSerial(Year(start_date), 4, 1)
    If start_date < x_date Then
        x_date = DateAdd("yyyy", -1, x_date)
    End If
    fnFinancialYearStart = x_date
End Function
 

hrdpgajjar

Registered User.
Local time
Tomorrow, 04:45
Joined
Sep 24, 2019
Messages
36
unfortunately, the first function need revision (rev.1A), it is always Comparing to the Current date
when it should be comparing to April of the passed date.

Code:
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
    Dim x_date As Date
    If start_date = 1 Then
        start_date = Date
    End If
    x_date = DateSerial(Year(start_date), 4, 1)
    If start_date < x_date Then
        x_date = DateAdd("yyyy", -1, x_date)
    End If
    fnFinancialYearStart = x_date
End Function
Done !
 

Users who are viewing this thread

Top Bottom