Greetings,
I am in need of more assistance.
My apologies if this is in the wrong place.
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
I sat down and wrote the following code but I can’t get it to work. I also looked into adding the code to the table as a field, but don’t know if it’s possible, as I couldn’t figure where to put it.
MIS = The name of the textbox in question.
If someone knows how to get this working, or a much simpler solution, I’d greatly appreciate the help.
Thank you,
MNM
I am in need of more assistance.
My apologies if this is in the wrong place.
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:
- No Resignation Date
- A Resignation Date in the future
- A Resignation Date in the Past
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
I sat down and wrote the following code but I can’t get it to work. I also looked into adding the code to the table as a field, but don’t know if it’s possible, as I couldn’t figure where to put it.
Code:
If [Resignation Date] = "" Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
Else
MIS = DateDiff("m", [Nichii Gakkan Start], Date)
End If
Else
MIS = DateDiff("m", [Nichii Gakkan Start], Date)
End If
ElseIf [Resignation Date] > Date Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
Else
MIS = DateDiff("m", [Nichii Gakkan Start], Date)
End If
Else
MIS = DateDiff("m", [Nichii Gakkan Start], Date)
End If
Else
If Month([Resignation Date]) = Month([Nichii Gakkan Start]) Then
If Day([Nichii Gakkan Start]) < Day([Resignation Date]) Then
MIS = DateDiff("m", [Nichii Gakkan Start], [Resignation Date]) - 1
Else
MIS = DateDiff("m", [Nichii Gakkan Start], [Resignation Date])
End If
Else
MIS = DateDiff("m", [Nichii Gakkan Start], [Resignation Date])
End If
End If
MIS = The name of the textbox in question.
If someone knows how to get this working, or a much simpler solution, I’d greatly appreciate the help.
Thank you,
MNM