How to categorize Quarters based on Start Date and End Date (1 Viewer)

Mahendra

Registered User.
Local time
Today, 05:44
Joined
Sep 19, 2013
Messages
62
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
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
11,613
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.
 

Mahendra

Registered User.
Local time
Today, 05:44
Joined
Sep 19, 2013
Messages
62
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.
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
11,613
You would nest the Month function inside the MonthName function:

MonthName(Month([EndDate]])
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
11,613
What have you tried in a query and what is going wrong?
 

Mahendra

Registered User.
Local time
Today, 05:44
Joined
Sep 19, 2013
Messages
62
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.
 

Mahendra

Registered User.
Local time
Today, 05:44
Joined
Sep 19, 2013
Messages
62
For the year part am using

Year([End Date])

but it's not working.
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
11,613
Hows it not working? Error message? Unexpected results? Ripping the space-time continium?
 

plog

Banishment Pending
Local time
Today, 07:44
Joined
May 11, 2011
Messages
11,613
Can you post your full SQL?
 

Mahendra

Registered User.
Local time
Today, 05:44
Joined
Sep 19, 2013
Messages
62
I got it..

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

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

Users who are viewing this thread

Top Bottom