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

[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 !

 

[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

hi there, can u pls give me the same code to get previous financial year data?

 

[Minty]

Just take a year off the current function?

 

[arnelgp]

you can use Query to fetch previous financial year data:

SELECT * FROM yourTableName Where [Material Sent Date]
BETWEEN DateAdd("yyyy",-1,fnFinancialYearStart(date)) And fnFinancialYearStart(date)-1

 

[hrdpgajjar]

you can use Query to fetch previous financial year data:

SELECT * FROM yourTableName Where [Material Sent Date]
BETWEEN DateAdd("yyyy",-1,fnFinancialYearStart(date)) And fnFinancialYearStart(date)-1

Thanks for the reply. but isnt it between fnFinanciyearStart And FnFinancialyearEnd ???

 

[hrdpgajjar]

Thanks for the reply. but isnt it between fnFinanciyearStart And FnFinancialyearEnd ???

you can use Query to fetch previous financial year data:

SELECT * FROM yourTableName Where [Material Sent Date]
BETWEEN DateAdd("yyyy",-1,fnFinancialYearStart(date)) And fnFinancialYearStart(date)-1

SELECT DataBase.[Regi No], DataBase.[Farmer Name], DataBase.Village, DataBase.Taluka, DataBase.District, DataBase.[MIS System], DataBase.[Area (Ha)], DataBase.[Sent to GGRC Date]
FROM [DataBase]
WHERE (((DataBase.[Sent to GGRC Date]) Between DateAdd("yyyy",-1,fnFinancialYearStart("date")) And fnFinancialYearStart("date")-1));



this gives "Data type mismatch" error

 

[GaP42]

You are passing the string "date" to the function fnFinancialYearStart which is expecting an actual date.

 

[hrdpgajjar]

ca

You are passing the string "date" to the function fnFinancialYearStart which is expecting an actual date.

can u help solving my problem ?

 

[GaP42]

You have incorporated the VBA provided by @arnelgp in post #7, #9. Why not look at the returned values from the call to the function in your code before inserting it into the SQL? Set up the variables so you can see them in your VBA before using the SQL and reference them in the constructed SQL. Use debug.print strSQL (if that is the name for the string) to view the string when executing. It should look similar to post #2 (some difference, given you are using arnel's function to get the dates). Missing the # in your SQL - you need to incorporate them to delimit/denote the date - in the format the database uses.