First of the Month, Following 2 Months in Query

[sainttomn]

Good afternoon,



I am working on converting a database from OpenOffice into MS Access, manually (my employer finally converted). I have managed to convert all my tables and I am working on my queries next. This has been going well, thus far, except for one sticking point.

the database records new employees and their start date (and dozens of other things).

In OpenOffice, I was able to use a piece of SQL that would basically add a "probabation period" indicator field.

For example:

Employee start Date = 1/15/2011
This employee will be in probation through January, and all of February and March. His indicator will stop as of 4/1/2011.

Employee Start Date= 1/1/2011
This employee will be in probation for January and February, and his indicator will stop on 3/1/2011.

Basically, the indicator will need to be present until the first of the month, following 2 months of employment.

I was able to accomplish this in OO using CASEWHEN and DAYOFMONTH commands, but I don't believe those work the same in Access.

If anyone can offer any assistance, I would appreciate it. I'm decent with SQL, but I haven't used Access in quite a while and I'm re-learning as I go.

Here's the basic code snipped in OpenOffice's SQL format, in case it might help:

Select "MyTable".*, TO_CHAR(CAST(CASE WHEN MONTH("Employee_Start_Date") > 9 AND DAYOFMONTH("Employee_Start_Date") >= 2 THEN YEAR("Employee_Start_Date") + 1 WHEN MONTH("Employee_Start_Date") <= 10 THEN YEAR("Employee_Start_Date") ELSE YEAR("Employee_Start_Date") + 1 END || '-' || CASE WHEN MONTH("Employee_Start_Date") + 3 >= 13 AND DAYOFMONTH("Employee_Start_Date") >= 2 THEN RIGHT('0' || MONTH("Employee_Start_Date") + 3 - 12,2) WHEN MONTH("Employee_Start_Date") + 3 < 13 AND DAYOFMONTH("Employee_Start_Date") >= 2 THEN RIGHT('0' || MONTH("Employee_Start_Date") + 3,2) WHEN MONTH("Employee_Start_Date") + 2 >= 13 and DAYOFMONTH("Employee_Start_Date") = 1 THEN RIGHT('0' || MONTH("Employee_Start_Date") + 2 - 12,2) WHEN MONTH("Employee_Start_Date") + 2 < 13 AND DAYOFMONTH("Employee_Start_Date") = 1 THEN RIGHT('0' || MONTH("Employee_Start_Date") + 2,2) END || '-01' as DATE),'MM/DD/YYYY') as "Indicator" From "MyTable"

Thank you,
-Tomn

[RedWolf3x]

Have you looked at this Thread? Perhaps it will help.

https://www.accessforums.net/access/...6th-14783.html

-Red

[June7]

Google: access sql case when
Here is one return
http://bytes.com/topic/access/answer...-sql-ms-access

[ssanfu]

AFAIK, SQL doesn't have a case statement, but I am far from being a SQL wizard or even advanced user.

My solution is to write a custom (VBA) function.

Create a new standard module. Name it something like 'MyFunctions".
Paste in the following code:

Function Get_EOP(pEmpStartDate As Date) As String
'get end of probation date

   Dim eopYear As Integer
   Dim eopMonth As Integer

   If Month(pEmpStartDate) > 9 And Day(pEmpStartDate) >= 2 Then
      eopYear = Year(pEmpStartDate) + 1
   ElseIf Month(pEmpStartDate) < 10 Then
      eopYear = Year(pEmpStartDate)
   Else
      eopYear = Year(pEmpStartDate) + 1
   End If

   If Month(pEmpStartDate) + 3 >= 12 And Day(pEmpStartDate) >= 2 Then
      eopMonth = Month(pEmpStartDate) + 3 - 12
   ElseIf Month(pEmpStartDate) + 3 < 13 And Day(pEmpStartDate) >= 2 Then
      eopMonth = Month(pEmpStartDate) + 3
   ElseIf Month(pEmpStartDate) + 2 >= 12 And Day(pEmpStartDate) = 1 Then
      eopMonth = Month(pEmpStartDate) + 2 - 12
   ElseIf Month(pEmpStartDate) + 2 < 13 And Day(pEmpStartDate) = 1 Then
      eopMonth = Month(pEmpStartDate) + 2
   End If

   Get_EOP = Format(CStr(DateSerial(eopYear, eopMonth, 1)), "mm/dd/yyyy")
End Function

The SQL statemnet would be:

Select MyTable.*, GetEOP([Employee_Start_Date]) as Indicator From MyTable

The VBA could be cleaned up... I tried to match what you have in the SQL to get it to return the same values.

[RedWolf3x]

You could try using Switch.

Switch(Month(Employee_Start_Date)>9 And Day(Employee_Start_Date)>=2,DateSerial(Year(Employ ee_Start_Date)+1,Month(Employee_Start_Date)-9,1),Month(Employee_Start_Date) And Day(Employee_Start_Date)=1,DateSerial(Year(Employe e_Start_Date),Month(Employee_Start_Date),2,1),Mont h(Employee_Start_Date)<=9 And Day(Employee_Start_Date)>=2,DateSerial(Year(Employ ee_Start_Date),Month(Employee_Start_Date)+3,1),Mon th(Employee_Start_Date)>10 And Day(Employee_Start_Date)=1,DateSerial(Year(Employe e_Start_Date)+1,Month(Employee_Start_Date)-10,1))

This should work.

-Red

[sainttomn]

Thank you all for your help. Multiple excelent solutions!