Access Calculations Based on dates

[neil12sc]

Dear Sirs/Madams,

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, traveling abroad, etc. However, when calculating service total leave taken for more than 3 weeks at a stretch will not be counted when calculating the 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 give 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 various 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.

 

[The_Doc_Man]

In Access, there is a function called "NZ" that allows you to detect null data and substitute something else for it. There is also an "IIF" function that could do some testing for you to determine that you had a blank (or a zero) in a particular field. Both functions work in queries and on forms. You just first have to decided what you REALLY want to do if you find that you have missing data. The reason Access presents nothing at all is probably because of nulls. Once a null is introduced to any computation, Access just propagates the null. But by using NZ to make nulls into something more manageable and by using IIF to detect a non-answer, you can substitute whatever you need.

HOWEVER, if you are doing this in a query environment, you can also use a WHERE clause with "IS NULL" or "NOT IS NULL" (as appropriate) to filter out cases you really don't want to compute. You might have "WHERE ( RTD2 NOT IS NULL) AND..." so that you don't even try for the bad cases. That is, of course, up to you.

 

[neil12sc]

Dear Sir,

I was testing this NZ without knowing how to apply it. I test it and come back to you tomorrow. I a, about to go to sleep.
Thank you very very much.

Meet you tomorrow.

Thank you again.

 

[The_Doc_Man]

@neil12sc - you apparently double-posted. This is something that happens now and again. Just be patient after you post your reply and wait for the site to confirm that your post made it. This happened to me a few times before I got used to the new forum software so I surely cannot fault you for having it happen. Just be patient and you will get used to XenForo's quirks. Again, this is not a slap at you, just a little bit of friendly advice that the forum software double-posts when you rush it too much. By the way, you can delete on instance of your doubled posts later. Look at the bottom of the post for the menu bar of options.

 

[neil12sc]

Dear Sir,

As I promised I did some testing. Your "NZ" proposal is some whar good.

However, I get some unbelievable answers when date fields are blanks Pl see what you can do. I will try an attached all the connected docs

See what you can do..

Thank you.

Wilfred

 

[The_Doc_Man]

Unfortunately, I don't know what you were expecting so can't tell you why you didn't get what you wanted. Not that I'm unwilling to help, but more that I don't know which way to go without more information.

 

[neil12sc]

Dear Sir,

Thanks for the prompt reply. If you look at the attachments, you will see that there are blank date fields. When the dates are blank answers are either some unbelievable numbers like 123 or - 122. Whereas the answers are correct when date fields are filled. Please enlarge the attachments I have sent yesterday. I will send 3 attachments now. The First 2 will gives replies to the codes in yesterday's 2nd attachment from the left. In today's attachments, the 9th gives all the date fields that have data. In the 10th attachment, some date fields are not filled. They give unrealistic answers. Unfilled fields say that this teacher has not taken leave or nor retired yet.

I feel it is something to do with the default value of the dates in the table, which I have attached as the 12th image. As the default value of date, I have put "0" (Zero).

But as you see the answers are unrealistic when dates are blank. Hope you will understand now. Please see what you can do.

I am finding it difficult to explain my problem without the live table, Forms code page, etc.

Thank you.

Wilfred.

 

[Pat Hartman]

When you use Nz() in a query and omit a return value, the return value will be 0. 0 in a date field = 12/30/1899 and that could result in "funny" values. So, you can't actually use Nz() when the field is a date, you need to use IsNull() and process the null appropriately.

FYI, the date data type is a double precision number. The integer portion is the Number of days since 12/30/1899. Negative numbers are dates prior to 12/30/1899 so 1 = 12/31/1899; 2= 1/1/1900, -1 = 12/29/1899, etc.

The decimal portion represents the time value so .5 = noon, .25 = 6 AM, .75 = 6 PM

 

[The_Doc_Man]

The point is that I can't do anything. The question is, when you have unrealistic dates, what do YOU want to do? As this is your database, you have to make a policy decision before I could hope to offer a solution.

Let me try it another way. You see something that you say is unreasonable. When you say that, the question becomes "Could you have detected earlier that it was going to be unreasonable and, if so, what would you do about it?" That is your DB, your problem, your design. When you have decided what is the right response to my question, I and my colleagues would be happy to make suggestions on how to implement what you seek.

You need to think about this way: What is Access? It is a tool that does what you tell it to do. What have you told it to do when something is wrong with the inputs? This is the center of the problem you face.

I understand that you are new to Access, but it is just another programming environment with some really nice visual features. But all Access knows how to do is maintain and show things like tables, queries, forms, reports, macros, modules, and relationships. These are all data concepts for storage, manipulating, showing, and enhancing your application. Access does not know anything about your application. It knows nothing about your employee policies. In that subject, Access is as dumb as a box of rocks. So you have to hold its hand all the way.

Our problem isn't that we don't understand that certain inputs give you unwanted results. Our problem is "What do you want it to do differently in that case?" and "Where do we start in LOOKING FOR that case?" When you can answer those questions, you are ready to proceed and we have some hope in being able to help.

 

[CarlettoFed]

The fundamental problem, as partially already reported, is in the structure you have given to the database. Data recording should be done, briefly, in three tables:

tblEmployees​

EmployeeID​

Surname​

FirstName​

SerialNumber​

tblPerformance​

PerformanceID​

EmployeeID​

StartDate​

Endate​

tblAssences​

IDAssenza​

EmployeeID​

StartDate​

Endate​

​

the calculations will then be made through a query.

 

[neil12sc]

Dear Pat Hartman,

Thank you for expressing your view.
I think after testing various ways to solve my problem in getting unrealistic values I think your idea might help me. So how can I use conditional formatting for my date fields if they do not have data? I think this might solve my problem.

Wilfred.

 

[Pat Hartman]

So how can I use conditional formatting for my date fields if they do not have data?

You are formatting a CONTROL when you use conditional formatting so the formatting is consistent for each control type regardless of whether or not it is bound or what type of field it is bound to.

What do you want conditional formatting to do to the textbox?

 

[neil12sc]

No, what I want is conditional formatting to do the date fields.

Some teachers take leave for exams weddings, Then are teachers who never take at all. So their fields like lef1, ret1, lef2, ret2 will have no data fill in these date fields. They will be blank. Therefore the final calculation with blank fields will be unbelievable numbers (either big minus big numbers) as an above image in 2021-02-13 image no 10.

Your 1st comment on my problem gave me the idea of the conditional format of dates.

Please help me.

 

[The_Doc_Man]

We are still dancing around the real problem. We can't tell you what to do, only HOW to do what you want to do.

Consider the case that you are lacking data such that you cannot accurately display the dates in question. How do YOU wish to show that? You have to decide what to show when you cannot show the "normal" answer. Do you need to show a blank? Do you need to show some sort of status word (like "Not Available" or "Unknown" or some other appropriate term)? This returns to the point of: What do you want to do about your unbelievable numbers? What display or response is appropriate?

Once you decide the practical side of this question, we can start pointing you to the technical answer. But there are many ways for this to go and you need to choose the response.

 

[neil12sc]

Dear Sir (The Doc Man)

Thank you for trying to help me.

No, It is not a question of displaying. In the attachment fig of 21-02-12 Figures 9 & 10, some date fields do not display any data, because these teachers have not taken any leave. So there is no question of rejoining again. Therefore Let1 (leave), Ret1 (Returned), etc will not have data - date.
When these fields are blank you will note calculation gives answers like -116 and 122 which is not true. But if you note the answers in image 9 of 12-2-2021 all the fields give realistic correct answers when all the date fields are having data.

This is my problem. How to avoid answers like -116 and 122 as in image 9. This is an extract from my test table which I have given top. As you can see it is not a question of the display. So I think it is a question of the conditional format of fields. When no data in dates how to code them for calculations. Please note the calculations are made in the form. They are not queries.

Hope you will understand now.

Thank you.

 

[Pat Hartman]

Therefore the final calculation with blank fields will be unbelievable numbers (either big minus big numbers)

Only when your database is not normalized forcing you to use code rather than queries and not properly handling null values. Here's an example.

The average of 3, 0, 3 = 2
But the average of 3, null, 3 = 3 When done using the Avg() function in a query. Select Avg(someField) as AvgGrade From yourTable

If you want to average three fields (a repeating group so the schema is not normalized) rather than three rows, you have to count the number of non-null values and sum only the non-null values so you need a complicated expression to even get the two numbers you will use to calculate the average.

 

[The_Doc_Man]

This is my problem. How to avoid answers like -116 and 122 as in image 9.

OK, I'm trying to get at a specific answer here, so let me ask the question this way:

In the case that you are getting -116 or 122, suppose that you were doing this by hand on a paper data analysis sheet. How would you handle this case by hand? What would you do and when would you do it?

 

[WAB]

Dear Sir (The Doc Man)

Thank you for trying to help me.

No, It is not a question of displaying. In the attachment fig of 21-02-12 Figures 9 & 10, some date fields do not display any data, because these teachers have not taken any leave. So there is no question of rejoining again. Therefore Let1 (leave), Ret1 (Returned), etc will not have data - date.
When these fields are blank you will note calculation gives answers like -116 and 122 which is not true. But if you note the answers in image 9 of 12-2-2021 all the fields give realistic correct answers when all the date fields are having data.

This is my problem. How to avoid answers like -116 and 122 as in image 9. This is an extract from my test table which I have given top. As you can see it is not a question of the display. So I think it is a question of the conditional format of fields. When no data in dates how to code them for calculations. Please note the calculations are made in the form. They are not queries.

Hope you will understand now.

Thank you.

Dear Neil
i agree with Pat... CarlettoFed already tried to put a normalized structure on your data - have you implemented that?
I think you make it more complicated than it is, because your data structure is wrong. It looks like you try to treat ACCESS like an excel spreadsheet. So again -you need to normalize these entities - then you can get whatever you want with simple sql queries (well date calculations are nor always so simple...).
The form you showed (joinedOn, leftOn, rejoined, left again) shows that the data structure is wrong. This need to go in a separate table. Its like you make fields like canTeachSubject1, 2, 3... and if someone is able to teach 4 or more subjects you run into problems. Then you need a second table (TeacherID, canTeachSubject). I do not know how much you looked into Data-Normalization and Entity-Relationship Models - but I think this is the key to get your application working.
In addition to that I may be not totally clear what you want to achieve - I would need to know the relation between yearly schooldays and the days the teacher was NOT present and categorize that (x days normal annual leave, x days sick, x days whatever reason and x days not granted
absence.
So:
1) Employees
I am missing fields here. Annual leave (days). When did the employee join the school - when did he leave the school (EMpJoin date / EMPleftschool date). Now this may be tricky and its a question how you look on it. The answer is: Does this employee still BELONG to the school (has he the right to come back... like taking 5 months off or so...) If this is the case 2 fields in the EMployee table are sufficient, cause you consider the employee still part of the school (otherwise you would need an employeeHistory table ( EMpID, joined school, left school with a composite primary key on the first two fields).
2) EmployeeLeave
EMPId
dateStartLeave
dateEndLeave
DAYS
granted (yes/no)
reason
comments

I am using this structure having another table LeaveReasons (annual leave, sick leave etc.).
If you now now how many schooldays you have in a year - you can make queries where you come up with
a) Absence in this year / absence since he joined school (in days)
b) Service provided in this year / service provided since he joined school (in days)

I attached a leave report from my database showing annual leave and other leave and comments...
Have fun!
Greetings
WAB