Date Calculation

[danielw.au]

Hi Everyone,

I have a database used to calculate the payout figure to employees when they leave our company. In this database I have the Hire Date and Separation Date.

In some cases we have to pay out Long Service leave and we need to break this down into three buckets.

1. Pre 16/08/1978.
2. Between 16/08/1978 and 17/08/1993
3. Post 17/08/1993

In a query, I need three expressions to calculate the total number of days for each bucket. For example, if the employee started on 01/01/1970 and finished on 23/04/2014, I would need total number of days for each group as follows:

1. 01/01/1970 to 15/08/1978.
2. 16/08/1978 to 17/08/1993.
3. 18/08/1993 to 23/04/2014.

For the life of my, I'm struggling to write this as an expression in a query.:banghead:

Any assistance would be greatly appreciated.

Kind Regards

Daniel

 

[namliam]

Something very simular to this solution: http://www.access-programmers.co.uk/forums/showthread.php?p=1345866#post1345866

Should help you too....

 

[bob fitz]

Without much confidence, you could try:

SELECT [COLOR="Red"][B]YourTableName[/B][/COLOR].[B][COLOR="red"]FName[/COLOR][/B], IIf(DateDiff("d",[[COLOR="red"][B]HireDate[/B][/COLOR]],CDate("16/08/1978"))<0,0,DateDiff("d",[[COLOR="red"][B]HireDate[/B][/COLOR]],CDate("16/08/1978"))) AS Bucket1, IIf(DateDiff("d",CDate("15/08/1978"),[[COLOR="red"][B]HireDate[/B][/COLOR]])>0 And DateDiff("d",[[B][COLOR="red"]HireDate[/COLOR][/B]],CDate("17/08/1993")>0),IIf(DateDiff("d",[[COLOR="red"][B]HireDate[/B][/COLOR]],CDate("17/08/1993"))>0,DateDiff("d",[[COLOR="red"][B]HireDate[/B][/COLOR]],CDate("17/08/1993")),0),DateDiff("d",CDate("16/08/1978"),CDate("17/08/1993"))) AS Bucket2, IIf(DateDiff("d",CDate("17/08/1993"),[[COLOR="red"][B]SepDate[/B][/COLOR]])<0,0,DateDiff("d",CDate("17/08/1993"),[[COLOR="red"][B]SepDate[/B][/COLOR]])) AS Bucket3
FROM [B][COLOR="red"]YourTableName[/COLOR][/B];

You will need to change the field and table names that are shown in red