How to categorize Quarters based on Start Date and End Date

Hello Friends,

Am struck with a problem in my project. Kindly help me out how to sort it out.

I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.

The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year

For Example,

If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).

2nd Example: We need to consider the End Date for classifying the quarters, Month and Year

If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).

The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.

Could any one send me the sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.

i will see the logic and will sort it out.

Please help me out

You don't store redundant data. So, all you need to store is the Start and End dates and then you calculate the rest. You can use the Date functions (http://www.techonthenet.com/access/functions/) to get Month/Year/Quarter/Week etc. based on those 2 pieces of data.

Thanks for the reply

For month I have seen that "MonthName (3)"

But in my case, How cane we refer to our end date in the open brackets.

You would nest the Month function inside the MonthName function:

MonthName(Month([EndDate]])

What have you tried in a query and what is going wrong?

Hey,

Got it..... I succeed with the month column. thanks for your guidance.

Could you similarly give me the formulas for finding out Year and Quarter too.

They are all on this page:

http://www.techonthenet.com/access/functions/

For the year part am using

Year([End Date])

but it's not working.

Hows it not working? Error message? Unexpected results? Ripping the space-time continium?

plog said:

Hows it not working? Error message? Unexpected results? Ripping the space-time continium?

Click to expand...

It displaying the values in the year column as

#Name?

Can you post your full SQL?

I got it..

DatePart("q"; ([Date]))

I missed ; and used , instead of that in the query