Fiscal Months

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,747
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 20, 2003
Messages
74
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

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

Board Regular
Joined
Jun 20, 2003
Messages
74
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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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.

Code:
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.

Code:
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:

Code:
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,650
Messages
5,660,120
Members
418,550
Latest member
kp_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top