update a field with a certain value if a condition is met

[GonzaloYagueSunol]

Dear all,



This is the original table and it is called "Dates":

Initial Date Final Date Days Permission
20/08/2015 22/08/2015
12/08/2015 30/08/2015
20/08/2015 18/08/2015

On one hand, I want to update the "Days" field with the difference (number of days) between "Final Date" and "Initial Date". On the other hand, if "Days" is greater than 15 "Permission" will be "Green", if it is between 15 and 0 "Permission" will be "Amber" and if it is lower than 0 "Permission" will be "Red". The desired result is shown below:

Initial Date Final Date Days Permission
20/08/2015 22/08/2015 2 Amber
12/08/2015 30/08/2015 18 Green
20/08/2015 18/08/2015 -2 Red

In order to achieve it, I am trying to run an update query. With the objective of making it easier, initially I just want Permission to be equal to Green if "Days">15. This is what I have done so far:


Field Days Permission
Table Dates Dates
Update to DateDiff("d",[Initial Date],[Final Date]) "Green"
Criteria DateDiff("d",[Initial Date],[Final Date])>15

I obtain the number of days but unfortunately "Permission" appears as "Green" always.

Thank you very much in advance for your help.

Best regards,

Gonzalo

[ranman256]

make it 2 queries, 1, set the days
Q1 to update the days , DateDiff("d",[Initial Date],[Final Date])

'then set the colors now that you have days.
to update [permisison] using the [Days] ,

Q2, update table set [permisison] = "Green" where [days]>15
Q3, update table set [permisison] = "RED" where [days]<16

[orange]

Why do you need this in a table?
You can calculate the number of days via a query. And you can determine if the difference is >15

  If (Datediff("d",[Initial Date],[Final Date])) > 15 Then
       'do something since > 15 days
       Permission = "Green"
    ElseIF (Datediff("d",[Initial Date],[Final Date])) <0 Then
      'do something else since <0 days [negative]
       Permission = "Red"
    Else
       Permission = "Amber"   '>=0 and <= 15
   End If