Custom Fiscal Calendar dates/fields

[pcride]

I need some fields in my form to auto populate with the current Fiscal Month and Week based on a table.

In Excel I created this very easy by doing a vlookup on a table I built based on WeekNum and matching those weeks up with the fiscal months and weeks.

How can I do this in access and have it do it automatically when someone enters a new record based on the Created Date.

Thanks,

 

[DCrake]

Here is a function that will get you the financial year. In this example the first day of the new financial year is 1st April. You may need to modify it if your day one is different.

Public Function FinancialYear(AnyDate As Date) As String

Dim fYear As String
If Month(AnyDate) < 4 Then
    FinancialYear = Year(DateAdd("yyyy", -1, AnyDate)) & "/" & Year(AnyDate)
Else
    FinancialYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy", 1, AnyDate))
End If
End Function

To calculate the financial month then use DateDiff() function using first day of financial year as week one.

David

 

[pcride]

Thanks, some of my months are 4 weeks and some are 5 weeks. Would this still work?

 

[pcride]

This would not work actually. What I need to do is populate the table with the Fiscal Months/Weeks based on a DLOOKUP , can anyone offere assistance?

Thanks,

 

[gemma-the-husky]

just have a table with your calendar, (similar to the datablock in your excel sheet) and dereference that when you need it.

have a table with columns for YEARNUM, PERIODNUM, PERIODENDDATE, and populate this as appropriate. (or you could hard code this in an array in a code module, which will be quicker, but slightly less flexible)

it probably wont be quite as simple though setting this up in access though, as using the same stuff in excel, altohugh it will be OK once you get the hang of it

 

[raskew]

Hi -

Thanks, some of my months are 4 weeks and some are 5 weeks. Would this still work?

What is your FY start-date, and the logic for developing your table? If it's consistent, it'd probably be possible to develop a dynamic lookup.

Bob

 

[pcride]

The fiscal year starts in July. I did discover DateDiff which could do the calculation but I think a look up would be much easier.

The fiscal month/week look up table would be static, I have a weekNum and I could use that to look up the fiscal month.

How would I start the look up given that I have a table build in access with the weekNum matchin up the Fiscal Months?

 

[DCrake]

Have you seen that pre populated intelligent dates mdb I posted in the sample databases you an youse that to fill in all the data you need.