0

I am using VBA to write 0/1 in a column of my table in Access. If the difference between the dates contained in the two columns [IsYTD?] and [SO Tarihi]) is <365 I would like to write 1 in [IsYTD?] while a 0 in the other case. This is my code for the moment but it doesn't seem to work. Can you help me?

   SQL3 = "UPDATE [1 - CURRENT RAW DATA SOURCE] SET [IsYTD?] = CASE " & _
    "WHEN ([IsYTD?]-[SO Tarihi])<365 THEN 1 " & _
    "ELSE 0 " & _
    "END"
    DoCmd.RunSQL SQL3
Erik A
  • 31,639
  • 12
  • 42
  • 67
Donats
  • 19
  • 4
  • 1
    Access doesn't support CASE expressions, you need to use [`IIF`](https://www.techonthenet.com/access/functions/advanced/iif.php) pr [`SWITCH`](https://www.techonthenet.com/access/functions/advanced/switch.php) instead. Possible duplicate of [Case expressions in Access](https://stackoverflow.com/q/772461/1048425). – GarethD Jul 04 '17 at 08:25
  • 1
    Possible duplicate of [Case expressions in Access](https://stackoverflow.com/questions/772461/case-expressions-in-access) – Andre Jul 04 '17 at 09:42

1 Answers1

1

CASE WHEN is not valid syntax in MS Access SQL. Use either switch or iif()

Since you only have 2 cases, iif is simpler to code

 SQL3 = "UPDATE [1 - CURRENT RAW DATA SOURCE] SET [IsYTD?] = iif(([IsYTD?]-[SO Tarihi])<365, 1,0)"
Thomas G
  • 9,886
  • 7
  • 28
  • 41