Hi I am Wilfred Cooray from Sri Lanka. (1 Viewer)

[neil12sc]

Dear all,

I am Wilfred Cooray from Sri Lanka, I found this forum while searching for a forum to get simple answers to problems. Hope I will be able to help you also.
My First problem with you is about Date calculation. The attachment is an Excel sheet that tries I am trying to describe the problem I have in Access 16.

This is about a Sunday school teachers (staff). Teachers are allowed to take leave for any valid reason like for examination, weddings, travelling abroad etc. However, when calculating service total leave taken for more than 3 weeks at a stretch will not be counted when calculating Service of the teachers. I am making a Database for Sunday school teachers with MS Access.

These codes are the fields in Access also.

Reg Registered date
Lef1 Left (date) to sit for an examination say (Part1)
Rtd1 Returned to school date
Lef2 Left to sit for the same (Part2) or another examination etc
Rtd2 Returned to school for the 2nd time.
Retd Retired Date.
Serv1 Service up to the 1st leave from Reg date.
Serv2 Service from the 1st Returned date (Rtd1) date to (Lef2) date
J+K Serv1+Serv2 (fields in Access for Service 1 and Service2)

Service to date taking Date() as a present date.
The last Column is Calculation Service to date assuming the teacher has not taken any leave.

In excel when fields from Lef1 to Rtd2 Not filled I got minus (-) values. In Access do not get any value at all. So, teachers, No 1 and 2 gives correct answers. But Teachers from 3 to 9 I got minus values and unbelievable values. Then teachers 10 and 11 Excel gives answers for Serv1, Serv2 and J+F and serve to date correctly.

I do not get any answer in Access when dates are missing or in other words, leave is not taken.

The code I used in Access for

Service to date - =Int(DateDiff("yyyy",[Reg],[Lef1])+DateDiff("yyyy",[Rt1],[Lef2])+DateDiff("yyyy",[Rt2],Date())/365.25)

I used this code in a form field

I have been trying many ways to get varies answers but as I said when dates are missing no answers are given in MS Access.

Can you please help me to solve this problem?

Thank you
Wilfred.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

Here are just a couple of tips for you:

1. Feel free to ask any question you like, however basic you may feel it is, or even if it has been answered before. Our expert members thrive on helping you out!

2. If you prefer a dark theme to the forums, just go to the bottom left of this forum and click "Default style". You will then see a selection of themes to choose from. I like Shades of Blue.

3. If you like any of the answers you get, feel free to click the "Like" link on the bottom right hand corner of the post. If you hover over the Like link, you can even choose the type of smiley.

Above all, hang around here, have fun, learn stuff and join in.

 

[JMongi]

Welcome to the forums!
The "rules" around here are pretty loose. Technically the welcome forum is not the place to post a question. You'll probably still get some help anyway.
To get the most exposure for your question it would likely be best to post a new thread in the appropriate forum area.

Welcome again!

 

[arnelgp]

can you attached a "real" excel file?

 

[theDBguy]

Hi. Welcome to AWF!

 

[neil12sc]

can you attached a "real" excel file?

Sorry, I tried this site does not allow the Excel file to be loaded I loaded a PDF file now.

Wilfred.

 

[neil12sc]

Welcome to the forums!
The "rules" around here are pretty loose. Technically the welcome forum is not the place to post a question. You'll probably still get some help anyway.
To get the most exposure for your question it would likely be best to post a new thread in the appropriate forum area.

Welcome again!

Ok I will do it.

 

[jdraw]

Welcome to AWF!

 

[Isaac]

Welcome

 

[WAB]

Dear all,

I am Wilfred Cooray from Sri Lanka, I found this forum while searching for a forum to get simple answers to problems. Hope I will be able to help you also.
My First problem with you is about Date calculation. The attachment is an Excel sheet that tries I am trying to describe the problem I have in Access 16.

This is about a Sunday school teachers (staff). Teachers are allowed to take leave for any valid reason like for examination, weddings, travelling abroad etc. However, when calculating service total leave taken for more than 3 weeks at a stretch will not be counted when calculating Service of the teachers. I am making a Database for Sunday school teachers with MS Access.

These codes are the fields in Access also.

Reg Registered date
Lef1 Left (date) to sit for an examination say (Part1)
Rtd1 Returned to school date
Lef2 Left to sit for the same (Part2) or another examination etc
Rtd2 Returned to school for the 2nd time.
Retd Retired Date.
Serv1 Service up to the 1st leave from Reg date.
Serv2 Service from the 1st Returned date (Rtd1) date to (Lef2) date
J+K Serv1+Serv2 (fields in Access for Service 1 and Service2)

Service to date taking Date() as a present date.
The last Column is Calculation Service to date assuming the teacher has not taken any leave.

In excel when fields from Lef1 to Rtd2 Not filled I got minus (-) values. In Access do not get any value at all. So, teachers, No 1 and 2 gives correct answers. But Teachers from 3 to 9 I got minus values and unbelievable values. Then teachers 10 and 11 Excel gives answers for Serv1, Serv2 and J+F and serve to date correctly.

I do not get any answer in Access when dates are missing or in other words, leave is not taken.

The code I used in Access for

Service to date - =Int(DateDiff("yyyy",[Reg],[Lef1])+DateDiff("yyyy",[Rt1],[Lef2])+DateDiff("yyyy",[Rt2],Date())/365.25)

I used this code in a form field

I have been trying many ways to get varies answers but as I said when dates are missing no answers are given in MS Access.

Can you please help me to solve this problem?

Thank you
Wilfred.

Dear Wilfred
it looks like you have the same structure in Access as you have in the excel sheet. I am running a database (with a lot of teachers) and made a much different approach. Is you table connected to a teacher table - and do you have any forms for input? I am simply tracking the days off. When a teacher is employed or leaves the school is in the teacher table - not in the teachers_leave table.
I just have a form where you can put the dates in when people go on leave and when they come back (here they do not work on Saturday - so I have a function excluding weekends when calculating days off...
We want also to track what kind of leave it was... or if the teacher was just absent..
So there is official ANNUAL LEAVE which got counted down (and of course we have here in Cambodia also all kine of other leave.. wedding... funerals.. sick leave... maternity... etc.). So I would also look into data-normalization of your access table.
Greetings
WAB