Fiscal Dates based on Calendar Date/Month

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
902
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,
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Same basic idea. Build a time lookup table (typically this gets called AllTime) with a RefDate field containing a date sequence, and other fields for FiscalDate, FiscalMonth, FiscalWeek, and anything else you might need.

In the form's record source, hook the AllTime table up to your CreateDate field (using RefDate). You can now place fields from the AllTime table onto the form. There should be no need to rewrite the data into the form's underlying table.

Two things to watch:
You're probably best off adjusting the join between the form's table and AllTime to display all records from <form table> and only those records from AllTime with matching data.
Set the AllTime fields so that users can't edit them (in the properties of the control). You don't want people messing with the table; it's purely a lookup.

Denis
 
Last edited:
Upvote 0
SydneyGeek thanks again... I have not performed any formulas in Access before.

Can you give me a brief explaination on where I put these and an example?

So if I am reading your post correctly I need the following

1 table with 4-5 columns
Fiscal Month
Fiscal Week
Calendar Month
Calendar Week (like Excels weeknum)

These would all align with each other.

Then what!! Thanks again you have been a great help.
 
Upvote 0
SydneyGeek thanks again... I have not performed any formulas in Access before.

Can you give me a brief explaination on where I put these and an example?

So if I am reading your post correctly I need the following

1 table with 4-5 columns
Fiscal Month
Fiscal Week
Calendar Month
Calendar Week (like Excels weeknum)

These would all align with each other.

Then what!! Thanks again you have been a great help.

Sorry for the delay but I have now tried four times to post to this thread and I keep getting bounced.

If you are more comfortable in Excel create the table there. Then...

1. Rename the worksheet tab to match the name of the table you want to create
2. Copy the data and paste into the Tables section of Access. When prompted about column headers, click Yes.

Once the table is in, build the query as I described before.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top