Update Query with system date

[ac277]

Hi,
I have an update query which update the date field with the current system date but the query returns zero.
Can somebody please see if there is anything wrong with my code?

UPDATE tblEmpChange SET tblEmpChange.ToDate = Date()
WHERE tblEmpChange.[cur_Status] Like '*Leave' and IsNull(tblEmpChange.[ToDate])
and tblEmpChange.[EmployeeId] in (Select tblEmpChange.[EmployeeID] From tblEmpChange WHERE tblEmpChange.[cur_Status] = 'Active' and
tblEmpChange.[FromDate] =Date());

 

[Golom]

These two conditions appear to be contradictory

UPDATE tblEmpChange SET tblEmpChange.ToDate = Date()

WHERE [red]tblEmpChange.[cur_Status] Like '*Leave' [/red]
  and IsNull(tblEmpChange.[ToDate]) 
  and tblEmpChange.[EmployeeId] IN 
   (SELECT tblEmpChange.[EmployeeID] 
    FROM   tblEmpChange 
    WHERE  [red]tblEmpChange.[cur_Status] = 'Active' [/red]
      AND  tblEmpChange.[FromDate] =Date());

If [cur_Status] is LIKE '*Leave' then it will not also be 'Active'.

 

[ac277]

Hi,
My query is trying to update the information for those employee who are on leave in the past but change back to "Active" now. I have all the old records for all the employee in the same table and when the new records come in, I need to update the old records by changing the ToDate field.
My query is joining back to the same table when it meets the criteria for the each EmployeeID

1) Any Old record where Cur_Status in ("Paid Leave","Leave","Oth Leave") and the ToDate = " "
2) Has another record where Cur_Status = "Active" and the FromDate = Current System (i.e. This record is new)

Your help is greatly appreciated

 

[Golom]

OK. If an EmployeeID was returned to 'Active' status on any [Fromdate] other than Today (i.e. Date()) then they won't be included but I assume that you have taken that into account.

Try running this. It should give you the records that are UPDATE candidates.

Select T1.[EmployeeID],
       T1.[cur_Status] As [Leave Status]       

From tblEmpChange T1 INNER JOIN tblEmpChange T2
     ON T1.EmployeeID = T2.EmployeeID

WHERE T1.[cur_Status] Like '*Leave' 
  AND IsNull(T1.[ToDate]) 
  AND T2.[cur_Status] = 'Active' 
  AND T2.[FromDate]   = Date());

 

[ac277]

Thank you so much. I will give it a try.