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

[hrdpgajjar]

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]

The SQL Where clause in a query would be something like

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]

The SQL Where clause in a query would be something like

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]

Ah okay, so you want it to calculate automatically based on the current date, from April to March Next year?

 

[hrdpgajjar]

Ah okay, so you want it to calculate automatically based on the current date, from April to March Next year?

Exactly what I need...

 

[Minty]

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

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]

create two function that will return the Starting date/ending date of financial year:

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:

Where DateValue([Material Sent Date]) Between fnFinancialYearStart() AND fnFinancialYearEnd()

 

[hrdpgajjar]

create two function that will return the Starting date/ending date of financial year:

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:

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]

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.

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]

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.

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 !