Displaying a Count in an Unbound Textbox on a Form

[MNM]

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:

• No Resignation Date
• A Resignation Date in the future
• A Resignation Date in the Past

See attached PDF
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.

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

 

[pr2-eugin]

MNM, your code is a bit confusing, can you give sample data covering the three scenarios and what you want the result to be? Something like.

Name    startDate    resignDate    noOfMonthEmp
Paul    01/03/2013    28/02/2014    11
John    01/05/2013    30/04/2014    10
Phil    11/06/2014                  ????

 

[MNM]

I did some digging online this morning (Tokyo, Japan time) and ran across a site that helped clarify at least where to put the code, Form, on Current event.
I ran some tests, and in the 'Paul' (not beginning of month start) & 'John' results were good.
'Phil' resulted in an Error 94, Invalid use of Null. However, "Null" is not used, only

If [Resignation Date] = "" Then

in the first line, even though this is not 'Null'. I thought I read this was how to handle empty fields.

Debugging, highlights:

 If Month([Resignation Date]) = Month([Nichii Gakkan Start]) Then

I changed this to an ElseIf block, and deleted an End If, but it still failed in the same place.

Now, on to how it should work...

Let's say today's date is 12/03/2014.
Paul's resignation has already passed.
John's resignation is approaching, so the code would use today's date as the second date in the DateDiff argument, after the month & day tests.
Phil has yet to turn in a resignation. Again, today's date is used as the second date in the DateDiff argument.
So, as the individual records are called up, the number of months employed would be as follows:

Name       startDate             resignDate               noOfMonthEmp
Paul         01/03/2013         28/02/2014                11
John         01/05/2013         30/04/2014               10
Phil          11/03/2013          ***                         13

{I can't get these to line up, I've tried, really.}

Now, this brings up something interesting, but extremely rare for my purpose. Paul actually worked 12 full months, ending employment on the last day of his contract, since his start date was the first day of the month (the extreme rarity, and one which may not ever happen here).

This is precisely why this scenario was chosen, yes?

So, in what direction do I need to go to compensate for the short-comings in my approach?

Regards,
MNM

 

[IanO]

I think you are being unnecessarily complex.
Why don't you try a simple query?
Your MIS part of the query would look like this:
MIS: IIf(Now()<[ResignDate], DateDiff('m',[StartDate],[ResignDate]), DateDiff('m',[StartDate],Now()))

The 'm' specifies month

The corresponding SQL would be:
SELECT Employee.EmpName, Employee.StartDate, Employee.ResignDate, IIf(Now()<[ResignDate],DateDiff('m',[StartDate],[ResignDate]),DateDiff('m',[StartDate],Now())) AS MIS
FROM Employee;

I am not sure about your reasoning about showing the person working one day less, but you can work it out.

By the way, it is better practice to use field names without spaces.
For instance NichiiGakkanStart or Nichii_Gakkan_Start

 

[MNM]

I think you are being unnecessarily complex.
Why don't you try a simple query?
Your MIS part of the query would look like this:
MIS: IIf(Now()<[ResignDate], DateDiff('m',[StartDate],[ResignDate]), DateDiff('m',[StartDate],Now()))

The 'm' specifies month

The corresponding SQL would be:
SELECT Employee.EmpName, Employee.StartDate, Employee.ResignDate, IIf(Now()<[ResignDate],DateDiff('m',[StartDate],[ResignDate]),DateDiff('m',[StartDate],Now())) AS MIS
FROM Employee;

I am not sure about your reasoning about showing the person working one day less, but you can work it out.

Contracts are for one full calendar year, i.e. March 3, 2014 - March 2, 2015 (always starts on a Monday), so just looking at the month can display the wrong amount.
The code given does not account for this. The above example would display 11 months if the person did not sign a new contract and the form was run on say May 21st.

By the way, it is better practice to use field names without spaces.
For instance NichiiGakkanStart or Nichii_Gakkan_Start

I've noticed this in samples, and have modified the tables accordingly, thanks.


However, upon looking at the error, I began to realize where the real problem was. I re-wrote the first line to:

If IsNull([Resignation Date]) Then

This fixed the problem with 'Phil'. Now I get an accurate months employed regardless of if the employee resigned or not, and if so when that was/will be.

Cheers,
MNM

 

[MNM]

The code so far:

If IsNull([ResignationDate]) Then
  If Month([NichiiGakkanStart]) = Month(Date) Then
    If Day([NichiiGakkanStart]) < Day(Date) Then
      MIS = DateDiff("m", [NichiiGakkanStart], Date) - 1
    Else
      MIS = DateDiff("m", [NichiiGakkanStart], Date)
    End If
  Else
    MIS = DateDiff("m", [NichiiGakkanStart], Date)
  End If
ElseIf [ResignationDate] > Date Then
  If Month([NichiiGakkanStart]) = Month(Date) Then
    If Day([NichiiGakkanStart]) < Day(Date) Then
      MIS = DateDiff("m", [NichiiGakkanStart], Date) - 1
    Else
      MIS = DateDiff("m", [NichiiGakkanStart], Date)
    End If
  Else
    MIS = DateDiff("m", [NichiiGakkanStart], Date)
  End If
Else
  If Month([ResignationDate]) = Month([NichiiGakkanStart]) Then
    If Day([NichiiGakkanStart]) < Day([ResignationDate]) Then
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate]) - 1
    Else
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
    End If
  Else
    MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
  End If
End If

As you can see, I modified the table names (and updated every reference in all queries, forms, and reports).

I do get a month difference for every case above. However, the value is incorrect for the 'Phil' and 'John' cases.

Today's Date: March 12, 2014
Example 1: An employee was hired on March 25, 2013. There is no resignation from them. The number of months employed is actually 11. The number of months displayed is 12, one too many.
Example 2: An employee on March 26, 2012. The date of resignation is March 25, 2014. The number of months should be 23, but 24 is displayed.

These are actual cases. How do I need to modify the code, or what is a better way, to achieve the aim?

Thank you for your patience,
MNM

 

[MNM]

Hello all,
The following code does it.
I've checked by altering the system time (with one employee who's contract expired on March 17, 2014). Every case so far has displayed correctly.

If IsNull([[ResignationDate]) Then
  MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
ElseIf [ResignationDate] > Date Then
  MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
Else
  If Month([ResignationDate]) = Month([NichiiGakkanStart]) Then
    If Day([NichiiGakkanStart]) < Day([ResignationDate]) Then
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate]) + Int(Format([ResignationDate], "dd") < Format([NichiiGakkanStart], "dd"))
    Else
      MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
    End If
  Else
    MIS = DateDiff("m", [NichiiGakkanStart], [ResignationDate])
  End If
End If

I just wanted to share,
MNM