If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Get fiscal year to show in a report
I have a database that I'm trying to get a report to sort on my fiscal year.
I have a module that I set up (below) to set the dates for my fiscal year. Function FiscalYear() As Date Dim dtFiscalYear As Date Dim strFiscalYear As String Dim intMonth As Integer intMonth = Month(Date) 'this will tell you the current month If intMonth 11 Then strFiscalYear = "11/1/" & Year(Date) - 1 Else strFiscalYear = "11/1/" & Year(Date) End If dtFiscalYear = CDate(strFiscalYear) FiscalYear = dtFiscalYear End Function I then add the "Between FiscalYear() And Date()" to the date fields in my queries. the queries are working great. My issue is that I'm trying to get my reports to give the year-to-date based on the fiscal year but they keep breaking them down to the calendar year. How do I set the sort feilds in the report to sort by my fiscal year of Nov 1 to Oct 31? |
#3
|
|||
|
|||
Get fiscal year to show in a report
Jeff,
I'm not trying to convert the year information per sai. I am trying to show a year-to-date report that will summerize the information by the fiscal year which runs 11/01 through 10/31. The dates remain as a date/time field, I just need to group them as stated above. "Jeff Boyce" wrote: David Although you are converting a date (e.g., 11/1) from one 'year' to another to get something you are calling a FiscalYear, it is still a date/time value. If you need to know the year (i.e., the "Fiscal Year"), why not just use the Year instead of the entire date? Check Access HELP for the Year() function. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "David McCormack" David wrote in message ... I have a database that I'm trying to get a report to sort on my fiscal year. I have a module that I set up (below) to set the dates for my fiscal year. Function FiscalYear() As Date Dim dtFiscalYear As Date Dim strFiscalYear As String Dim intMonth As Integer intMonth = Month(Date) 'this will tell you the current month If intMonth 11 Then strFiscalYear = "11/1/" & Year(Date) - 1 Else strFiscalYear = "11/1/" & Year(Date) End If dtFiscalYear = CDate(strFiscalYear) FiscalYear = dtFiscalYear End Function I then add the "Between FiscalYear() And Date()" to the date fields in my queries. the queries are working great. My issue is that I'm trying to get my reports to give the year-to-date based on the fiscal year but they keep breaking them down to the calendar year. How do I set the sort feilds in the report to sort by my fiscal year of Nov 1 to Oct 31? . |
#4
|
|||
|
|||
Get fiscal year to show in a report
Jeff,
I guess I should have titled this differently. It should have said "Get a report to sort/group by a fiscal year." "David McCormack" wrote: Jeff, I'm not trying to convert the year information per sai. I am trying to show a year-to-date report that will summerize the information by the fiscal year which runs 11/01 through 10/31. The dates remain as a date/time field, I just need to group them as stated above. "Jeff Boyce" wrote: David Although you are converting a date (e.g., 11/1) from one 'year' to another to get something you are calling a FiscalYear, it is still a date/time value. If you need to know the year (i.e., the "Fiscal Year"), why not just use the Year instead of the entire date? Check Access HELP for the Year() function. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "David McCormack" David wrote in message ... I have a database that I'm trying to get a report to sort on my fiscal year. I have a module that I set up (below) to set the dates for my fiscal year. Function FiscalYear() As Date Dim dtFiscalYear As Date Dim strFiscalYear As String Dim intMonth As Integer intMonth = Month(Date) 'this will tell you the current month If intMonth 11 Then strFiscalYear = "11/1/" & Year(Date) - 1 Else strFiscalYear = "11/1/" & Year(Date) End If dtFiscalYear = CDate(strFiscalYear) FiscalYear = dtFiscalYear End Function I then add the "Between FiscalYear() And Date()" to the date fields in my queries. the queries are working great. My issue is that I'm trying to get my reports to give the year-to-date based on the fiscal year but they keep breaking them down to the calendar year. How do I set the sort feilds in the report to sort by my fiscal year of Nov 1 to Oct 31? . |
#5
|
|||
|
|||
Get fiscal year to show in a report
I'm not sure if this works for your situation but a nice way to deal with
fiscal type calculations is to have a time dimension table. Where you have one record for each day of the year for as many years as you need. It's a common data warehousing technique. So for example you could have columns for date, fiscal year, fiscal month, fiscal week, etc.... Usually you would run some kind of code to create this table (one time setup). I did a quick search and didn't find a great bit of code but this might give you the idea: http://www.sqlbook.com/Data-Warehous...script-18.aspx Then you just always join this table into the mix (by joining by date) to help with knowing what fiscal year, fiscal month, fiscal quarter, holiday or all kinds of other type of information (day of the year etc...). You other approach is to do calculations in the query itself but they get to be a real pain if you have to do it too often. iif(Month(TheDate) = 11, .........) HTH, -- Mark Andrews RPT Software http://www.rptsoftware.com http://www.donationmanagementsoftware.com "David McCormack" David wrote in message ... I have a database that I'm trying to get a report to sort on my fiscal year. I have a module that I set up (below) to set the dates for my fiscal year. Function FiscalYear() As Date Dim dtFiscalYear As Date Dim strFiscalYear As String Dim intMonth As Integer intMonth = Month(Date) 'this will tell you the current month If intMonth 11 Then strFiscalYear = "11/1/" & Year(Date) - 1 Else strFiscalYear = "11/1/" & Year(Date) End If dtFiscalYear = CDate(strFiscalYear) FiscalYear = dtFiscalYear End Function I then add the "Between FiscalYear() And Date()" to the date fields in my queries. the queries are working great. My issue is that I'm trying to get my reports to give the year-to-date based on the fiscal year but they keep breaking them down to the calendar year. How do I set the sort feilds in the report to sort by my fiscal year of Nov 1 to Oct 31? |
Thread Tools | |
Display Modes | |
|
|