Fiscal Month / Financial Periods

[access_numpty]

Using Access 2000

I have a field which lists a date. I would like to determine which financial month the date is.

However the financial month starts and ends at odd times, for example

The start time of the month is the first Sunday after the last Friday of each month. With the end date being the day before this.

So for July it would have been

30/06/2013 - 03/08/2013

and August is

04/08/2013 - 31/08/2013

I would like the output to show something like "July 2013"


Does anyone know how to do this?

Many thanks in advance

 

[stopher]

An easy solution is to create a table listing your fiscal calendar:

CalDate_______FiscalMonth_____FiscalYear
30/6/13_________July____________2013
31/6/13_________July____________2013

etc

Then just use the table as a joined table in your queries. By the same token you can add other "time" fields such as FiscalQtr.

Obviously your calendar needs to be a full date range for which you are likely to report on. Also best to make CalDate a primary key.

hth
Chris

 

[access_numpty]

There is a table present already.

Which lists

Month Year Start Date End Date
Jul13 30.06.2013 03.08.2013

However I would have no idea how to join it to a different table?

 

[plog]

I vote a function. This will work without having to manually populate anything. Then all you would have to do is pass it a date and it will return what you want.

I did something similar for someone else--it calculated the last tuesday of the next month. You could use that code as the basis for determining what you need:

http://www.access-programmers.co.uk/forums/showthread.php?t=248885

 

[stopher]

There is a table present already.

Which lists

Month Year Start Date End Date
Jul13 30.06.2013 03.08.2013

However I would have no idea how to join it to a different table?

You can do this using a cartesian join (a.k.a. cartesian product). Do this by adding the to tables to a query design but do not join them. Then use a where clause (criteria) to ensure the to only select rows where your date is in between the start and end dates.

But I still recommend the approach I gave in my first post. The problem with thre cartesian join is that it can produce huge numbers of rows (since it is a multiple of the rows in both tables).