Fiscal Months

[GorD]

Is there a function similar to excels vlookup that would allow fiscal months to be found from a date, within access.

I was using excel to do this by now have 80000 lines of data so have moved the data into access and create a pivot table from within excel.

In excel I had a lookup table and used an non exact match to return the fiscal month.

Can't see how to do that in an access query/calculation - but am fairly new to access.

Thanks a lot

 

[Joe4]

If you tell us a little about the structure of your data, and the fields in the two tables you are trying to link, we may be able to help you devise a solution.

 

[F1A1G1inDC]

Fiscal Date Table

I face a similar issue with my database. I have termination dates and hire dates which are, of course, based on a calendar year...but all the reporting is based on the fiscal calendar. My workaround on this was I did a table (started it in XL and imported to Access) with several columns:

Calendar Date Fiscal Month Fiscal Day Fiscal Quarter Fiscal Year
04/05/2006 April 6 1 2007

I matched up my calendar date with the termination/hire date to return several different options for folks to look at the data. They can look at it as April, first quarter, 6th day of the fiscal year, etc. I even went a step further to do a field with "1Q FY07". This is especially useful if you are using any type of OLAP to give different ways to slice and dice without a lot of coding.

HTH.
-F1A1G1

 

[GorD]

Did your table consist of an entry for every date or just the end/start of each period.

In excel a year can be covered by 12 dates, but would I need 365 for access??

 

[VicRauch]

You can use for your criteria within a query the "Between x and y" syntax to look for something within a range. I believe this would give you the same type of function available within the VLookup in Excel.

 

[F1A1G1inDC]

My table had an entry for every date (I started with the first fiscal day and dragged down and then did an edit-fill-down for the other data). I "could" have gotten away with only 104 dates (Mondays for hires and Saturdays for terminations)...but there is always that "oddball" policy breaker so I figured better safe than sorry.

 

[mdmilner]

No, you would not need an entry for every date. Nothing really wrong with doing one record per day - at best, using only 12 dates to define each month is just more 'elegant'.

The approach I took is detailed below, however, I established an end date with my own set of rules as to how I defined it. In my case, each of these dates are the end date - only values prior to it are valid and I use successive entries to define a range of valid points. I've also coded in a few other date groupings in one shot...ie, the Closeout, Monthly, Quarterly & Weekly entries in my select case logic. This particular segment of code was written to support a report handling database - it manages the import & transformation of data into a final format. This SQL was merely a reminder list - to remind me of what I haven't done yet.

I'm going to abbreviate this example quite a bit starting with just the parameter portion of a SQL statement.

WHERE (((DatePuller([dteStamp], [ScheduleType])) >= DateSerial(Year(DatePuller([dteStamp],[ScheduleType])), 1, 1) And (DatePuller([dteStamp], [ScheduleType])) <= Date())) And Not IsNull([dteStamp]) And Not IsNull([ScheduleType])

This is the full SQL, the above is noted separately to highlight the critical portion.

SELECT [task_name], [dteStamp], [ScheduleType], DatePuller([dteStamp],[ScheduleType]) AS Pending
FROM tblTask
WHERE (((DatePuller([dteStamp], [ScheduleType])) >= DateSerial(Year(DatePuller([dteStamp],[ScheduleType])), 1, 1) And (DatePuller([dteStamp], [ScheduleType])) <= Date())) And Not IsNull([dteStamp]) And Not IsNull([ScheduleType])
GROUP BY [task_name], [dteStamp], [ScheduleType], disable
HAVING (((tblTask.ScheduleType) <> 'None') AND (disable=False))
ORDER BY [task_name], [dteStamp], [ScheduleType];

DatePuller is a public VBA code module that looks like this:

Public Function DatePuller(Optional ByVal dtMine As Date, _
                           Optional ByVal strType As String) As Date
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strVal As String
Dim intVal As String

On Error GoTo HandleErr
Set dbs = CurrentDb
'dtMine = #12/13/2005#           ' This is a test item comment out to not use

If Not IsNull(dtMine) Then
 Select Case strType
  Case "Closeout"
     strSQL = "SELECT TOP 1 * FROM tblCloseout WHERE sched_closeout >= #" _
               & dtMine & "# ORDER BY sched_closeout"
    Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    If rs.RecordCount > 0 Then
      DatePuller = rs.Fields(0).Value
    Else
      Debug.Print "problem"
    End If
  Case "Monthly"
     intVal = Month(dtMine)
     DatePuller = DateSerial(Year(dtMine), (intVal + 1), 1) ' 1st Monday of Month
     Select Case Weekday(DatePuller)
       Case 2: DatePuller = DatePuller
       Case Is > 2: DatePuller = DatePuller + Weekday(DatePuller) - 5
       Case 1: DatePuller = DatePuller + 1
       Case Else
     End Select
  Case "Quarterly"
     intVal = Month(dtMine)
     intVal = Int((intVal + 2) / 3) ' Identifies Current Quarter
     DatePuller = DateSerial(Year(dtMine), ((intVal) * 3 + 1), 1) ' 1st Monday of Quarter
  Case "Weekly"
     Select Case Weekday(dtMine)
       Case 2: DatePuller = dtMine + 7
       Case Is > 2: DatePuller = dtMine - (Weekday(dtMine) - 2) + 7
       Case Is = 1: DatePuller = dtMine - (Weekday(dtMine) + 1) + 7
       Case Else
     End Select
  Case Else:
 End Select
Else
 DatePuller = #1/1/2004#
End If

Set rs = Nothing
Set dbs = Nothing
ExitHere:
    Exit Function

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 12-17-2004 15:43:39   'ErrorHandler:$$D=12-17-2004    'ErrorHandler:$$T=15:43:39
HandleErr:
    Select Case Err.Number
        Case 2001:
           MsgBox "Found My Error"
           Resume Next
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "modUtil.DatePuller" 'ErrorHandler:$$N=modUtil.DatePuller
    End Select
' End Error handling block.
End Function

This is what tblCloseout looks like currently.

sched_closeout	period	acctyear
8/27/2004	8	2004
9/24/2004	9	2004
10/22/2004	10	2004
11/24/2004	11	2004
12/30/2004	12	2004
1/28/2005	1	2005
2/25/2005	2	2005
4/1/2005	3	2005
4/29/2005	4	2005
5/27/2005	5	2005
6/30/2005	6	2005
7/29/2005	7	2005
8/26/2005	8	2005
9/30/2005	9	2005
10/28/2005	10	2005
11/25/2005	11	2005
12/30/2005	12	2005
1/27/2006	1	2006
2/24/2006	2	2006
3/31/2006	2	2006
4/28/2006	4	2006
5/26/2006	5	2006
6/30/2006	6	2006
7/28/2006	7	2006
8/25/2006	8	2006
9/29/2006	9	2006
10/27/2006	10	2006
11/24/2006	11	2006
12/29/2006	12	2006