Calculating time

[René]

I am trying to set up my reports and forms to tell me how long my employees have worked for me. I added a field that says Term Date and another that just say Today. I can query In-Active employees to get a date, this one is easy as they have a term date listed. How do I have the form show emp length, it would have to be an if statement of some sort, if Term Date is null then Today - Hire Date . etc

For the active employees I was using thi

[Emp Length] = DateDiff("yyyy", [Hire Date], Now()) + Int(Format(Now(), "mmdd") < Format([Hire Date], "mmdd")

I tried to add decimals but that didn't work, I also replaced the "yyyy" with "mmmm" and that didn't work either

by the way, I'm a beginner but pretty stubborn! Any help is greatly appreciated

 

[René]

Ooops, I am trying to get it to show me in months not years for those Active Emp.s

 

[Steve Schapel]

René,

Try:
DateDiff("m",[Hire Date],Date())

- Steve Schapel, Microsoft Access MVP

 

[René]

You are sooo awesome, I've been beating my head on that one. One tweaking question, please -- how to show in years and months if greater than 1 year

Thanks in advance

 

[René]

I now have an error message, not sure what I did butwhen I either close the form I get the following message - the expression on Unload you entered as the event property settingproduced the following error: A problem occurred while Microsoft Access was communicating with the OLE Server or Active X control -- Help

 

[Steve Schapel]

René,

This is unlikely to have anything to do with your DateDiff()
expression. It seems there is a macro or VBA event procedure assigned
on the Unload event of the form. You will need to have a look in the
form design and let us know what is happening there before anyone in
likely to be able to assist further.

- Steve Schapel, Microsoft Access MVP

 

[René]

I'm sure that I boubled it somehow, this is the code on the form in question, should I take the subform off the main forma nd put it in again?

Sub Form_Load()
On Error GoTo Form_Load_Err

If ParentFormIsOpen() Then Forms![Employees]!ToggleLink = True

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit

End Sub
Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_Unload_Err

If ParentFormIsOpen() Then Forms![Employees]!ToggleLink = False

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Error$
Resume Form_Unload_Exit

End Sub
Private Function ParentFormIsOpen()

ParentFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "Employees") And acObjStateOpen) <> False

End Function


Private Sub Hire_Date_AfterUpdate()
[Emp Length] = DateDiff("m", [Hire Date], Date)

End Sub

 

[René]

Is there a way to make it specific ie. 1 yr 2 mos etc in the expression??

by the way thanks for all your help

 

[Steve Schapel]

René,

Hmmm. I can't see what the problem is, but then I don't really see
what this code is trying to achieve. What is the Employees form? And
which form is this code running on?

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

René,

Dim BaseMonths As Integer
Dim EmpYears As Integer
Dim EmpMonths As Integer
BaseMonths = DateDiff("m", Me.Hire_Date, Date)
EmpYears = BaseMonths \ 12
EmpMonths = BaseMonths Mod 12
Select Case EmpYears
Case 0
Me.Emp_Length = ""
Case 1
Me.Emp_Length = "1 yr "
Case Else
Me.Emp_Length = EmpYears & " yrs "
End Select
Me.Emp_Length = Me.Emp_Length & EmpMonths
Select Case EmpMonths
Case Is < 2
Me.Emp_Length = Me.Emp_Length & " mo"
Case Else
Me.Emp_Length = Me.Emp_Length & " mos"
End Select

- Steve Schapel, Microsoft Access MVP

 

[René]

Good Afternoon,

I inserted the code above into the event procedure where I previously had DateDiff("m", Me.Hire_Date, Date)
I got a run time error and the debugger put me at

Case Else
Me.Emp_Length = EmpYears & " yrs "

Do I need a field/text box etc of some type called EmpYears and/or EmpMonths

 

[Steve Schapel]

René,

What did the error message say? What is the data type of your Emp
Length field?

- Steve Schapel, Microsoft Access MVP

 

[René]

Run Time Error '-2147352567 (800200009)
Field 'Employee Requirements.Emp Length cannot be zero-length string

Emp Length was a number field but changed to text

 

[René]

I changed the data type back to number and this is the error msg that flies with that

The value you entered isn't valid for this field

the debugger hits

Me.Emp_Length = Me.Emp_Length & " mos"

 

[Steve Schapel]

René,

OK, you can either change the Allow Zero Length property of the Emp
Length field in design of the table to Yes, or change the code as
follows:
Dim BaseMonths As Integer
Dim EmpYears As Integer
Dim EmpMonths As Integer
Dim LengthHolder As String
BaseMonths = DateDiff("m", Me.Hire_Date, Date)
EmpYears = BaseMonths \ 12
EmpMonths = BaseMonths Mod 12
Select Case EmpYears
Case 0
LengthHolder = ""
Case 1
LengthHolder = "1 yr "
Case Else
LengthHolder = EmpYears & " yrs "
End Select
LengthHolder = LengthHolder & EmpMonths
Select Case EmpMonths
Case Is < 2
LengthHolder = LengthHolder & " mo"
Case Else
LengthHolder = LengthHolder & " mos"
End Select
Me.Emp_Length = LengthHolder

- Steve Schapel, Microsoft Access MVP

 

[René]

You're Awesome! Thanks

 

[René]

I was able to set the string in the first table and the employee field is now working

I tried to set the same thing up in the customer table and was unable to get it working the field only said "mos" upon update, so I copied that second set of code that you gave me, switched out the Hire Date for Svc Start and it works great

I left EmpYears and EmpMonths untouched will this have any affect? or should it be changed to ScvYears etc.

Dim EmpYears As Intege
Dim EmpMonths As Integer

 

[Steve Schapel]

I left EmpYears and EmpMonths untouched will this have any affect? or should it be changed to ScvYears etc.?

No problem, these are only variables created for the duration of the
code running, so what they are called doesn't matter.

- Steve Schapel, Microsoft Access MVP