Showing years,months,days

[Lynx2x]

Hello to everyone here on forum!

I have a little problem. I have a form where I can see all the employees and informations about them. There is also text box wich has information from what date worker is in our company and another text box wich has information how many years worked in any other company.
Than there is a third text box that calculates from previous two how many years worker is employeed all togheter.
Every thing is ok, but I would need that third text box would be showing not just years but : YEARS / MONTHS / DAYS

Does anyone know how to do that? Thank you very much in advance and sorry for my clumsy Englih

Below is the code that I'm using now:

Private Sub AllTogheterWork_Enter()
Dim sdd As Integer
Dim year As String

sdd = DateDiff("yyyy", Me.DateHere, Date)
If Me.PrevWork <> 0 Then
Me.AllTogheterWork.Value = sdd + Me.PrevWork
Else
Me.AllTogheterWork.Value = sdd
End If
End Sub

 

[pr2-eugin]

Hello Lynx2x,

Pat Hartman, a very reliable and resourceful member of this Forum, has some Useful Date/Time functions.. Which I believe also has the Age calculation function which you might be interested in.. Good Luck..

 

[missinglinq]

DateDiff using "yyyy" simply subtracts the beginning year from the current year. If DateHere was 12/31/2012 and today was 1/1/2013, your result, using "yyyy," would be one year, when, in fact, it only covers one day!

Calculating YEARS/MONTHS/DAYS would be done using DateDiff() with the Days interval ("d") for the first argument, and then parsing the results into YEARS/MONTHS/DAYS. This will work for the time worked in your company, but if the Field that holds the number of years worked in other companies holds just that, years, your calculation of YEARS/MONTHS/DAYS is going to be incorrect, unless you actually have start/end dates for the prior employment, rather than simply 'years.'

Doug Steele has a 'more complete' version of the DateDiff function that can do exactly what you want, in one go, as far as the YEARS/MONTHS/DAYS calculation goes:

http://www.accessmvp.com/djsteele/Diff2Dates.html

Linq ;0)>

 

[Lynx2x]

Thanks for the replies guys. I will have a look at the codes you've pointed me at and let you know if it is working.

 

[Lynx2x]

Hello again!

So, I've used code from Pat and it is working fine but there is a littlle problem. It is working if text box where difference in years, months, days, is written is Unbound so that I dont have Control source. If I want to have control source so that difference would be recorded in tabel I get error that says -2147352567-The value you entered isn't valid for this field.
Any ideas why is that?

Thank you very much for all of your help.

 

[Mihail]

No need to store this things.
You can calculate this again, and again, and again... using Pat's routines.

By the way, I wish to say a big THANK YOU to Pat.

 

[Lynx2x]

Yes, big thanks to Pat Hartman from me also

And yes...I need to store those differences for every employee, to see what is his employment age. If you know what I mean.

Thank you all for helping

 

[Lynx2x]

Ok, I've was trying a little bit and figured out that with Pats code or anything with Unbound isnt wrong.
So, when I open my form I have a list box of people and firts one shown is lets say Mike Mickey. If I dont do anything else but calculate date difference for Mike Mickey everything is ok and result is written into the table. But if I chose any other employee from list box and try to calculate dateDiff, then I get the error Update or CancelUpdate without AddNew or Edit.

Please help me if you can. I really dont know what to do. Big thanks to all good people on this forum.

 

[Mihail]

Yes, big thanks to Pat Hartman from me also

And yes...I need to store those differences for every employee, to see what is his employment age. If you know what I mean.

I know what you mean, but, also, I know that this is very very VERY VERY bad for your database and the future development.
Can you see now the employment age ? Yes you can. And it is not yet saved in your database. Pat's routines have calculated this. Do you think that Pat's routines will fail after 100 years ? I don't think so. So, if you can calculate, why to store in the database ?

Maybe, someone with a better English than mine, can explain you what I mean.

 

[Lynx2x]

I know what you mean and I understand you, but like I said, it must be stored and I is doing that until I change employee, so I think something is wrong with list box.
I appreciate your help!

 

[Mihail]

Ok. You are warned.
How you try to write this into table ?

 

[Mihail]

Also, show us the table structure, the fields properties and, if exists, the indexes.

 

[Lynx2x]

Like I said. It is working fine until I pick any other employee from the list box, than first record that is given. Then I get the update error.
So something is wrong with the list box, I asume.
List box has Embedded Macro:

Record First
Where Condition ="[MATICNA] = " & Str(Nz([Screen].[ActiveControl];0))

Mybe I should do it in the code somehow and use If Me.dirty then Me.dirty = False.

I will show you my table once I get home from work.

Thank you

 

[Galaxiom]

I know what you mean and I understand you, but like I said, it must be stored

Please excuse a perhaps frivolous presentation of a serious subject.

New developers often seem sympathetic to the poor computer believing that making it work out those figures every time they are required is cruel and unusual punishment so they feel they should store the derived values.

The reality is that computers are bored most of the time. They can perform billions of operations in a second so please do give them something to do.

Stored calculations are really only done when the user can't wait for the computer to do the job each time. Believe me, this only happens at levels of calculation that far exceed anything most of us would anticipate, particularly as novices ..... provided the data is properly structured and the task is intelligently designed.

Even after years of experience I am still regularly stunned by how quickly an ordinary computer can process properly structure information. There have been several times when I went looking for errors because presumed that something was very wrong when what seemed enormous tasks were completed in a few seconds.

When something takes along time don't start thinking about storing the results, start looking at how you can make the task more efficient because when something takes more than a few seconds it is usually the design of the data or the data processing that need to be refined.

 

[Lynx2x]

Hello again!

First of all sorry for not replying for a week. I had so much work at job and at home that I basicly didn't have time.

Everything is working now as it should and I get difference between two dates.

And now, lets say I get result 2 years 3 monts 15 days and I would like to add to this result 4 years 1 month 3 days (how long employee worked at another company).
Is it possible to do that and how? Should I extract the number from text of first result and add second numbers? Or is it possible to do this in Pats code automaticly? (thank you again Pat )

Any ideas? Thank you guys so much, you've helped me a lot so far!