How to Calculate Leaves Left

[Faiza]

I have a query table "Casual Leave Query"

Employee ID	From Date	To Date	No of Days	Leaves Left
1	6/4/2013	6/4/2013	1	20-1=19
1	6/5/2013	6/5/2013	1	19-1=18
1	6/7/2013	6/8/2013	2	18-2=16
2	6/13/2013	6/14/2013	2	20-2=18

I have calculated No of Days with this formula No of Days: (DateDiff("d",[From Date],[To Date])+1)

I am unbale to calculate "Leaves Left" HOw can I calculate Left Leaves ?
Help me!

[rpeare]

I wouldn't do the running sum in a query, is that a requirement?

There's the ability to do running sums in reports.

The short answer is that you'd have to use a domain function like

Leaves Left: 20-DSum("(DateDiff(""d"",[From Date],[To Date])+1)","tblTest","[From Date] <= #" & [from date] & "#")

You'd just have to substitute your table name to get it to work.

Of course you're likely going to have to add the criteria of the employee ID unless you're only querying one person at a time with this query.

One other note, you may want to consider removing any spaces from your object names (field names, table names, query names, etc) they cause a lot of problems if you're not super aware of them and it's easier just to use an underscore (_) wherever you want a space.

[Faiza]

Thanks a lot... it helped me, I can use it in report. You are true I must add some criteria for each employee ID otherwise for Employee ID=2 it shows leaves left=14.... How can i set this criteria for every Employee ID...i.e for each employee this calculation repeats......
Once again looking for your help please.
Secondly I shall keep in mind your advice to use underscore (_) for every field name.....
Thanks for your advice.

[rpeare]

Leaves Left: 20-DSum("(DateDiff(""d"",[From Date],[To Date])+1)","tblTest","[From Date] <= #" & [from date] & "# AND [EmployeeID] = " & [EmployeeID])

Assuming your employee ID is a number (autonumber) field.

[Dunro]

Have you considered if you will need to add/reset the leaves counter? Having a fixed constant (20) may cause you pain in the future.

[sdc1234]

I have a similar issue, the only difference is the days which is 60 days I am using the code =60-DSum("(DateDiff(""d"",[StartDate],[EndDate])+1)","IAMaster","[StartDate] <= #" & [StartDate] & "#") and putting in in the control sourse of the property field in my reports, my #'s are way off, am I putting the script in the right place, I am new to access and just trying to make this work. please help

Thanks

[rpeare]

You should post your own thread but you are summing ALL records in your table IMASTER table with a start date earlier than whatever date you put in. So even if your query is looking at one person, the syntax of your dsum statement is ignoring that, it's summing all the records in the table with a start date lower than the current record which is likely not what you want. You have to provide the dsum statement with ALL criteria (employeeID, etc)