-2

I have a T-SQL query that I need to change to an Access query. I tried a lot of times to change it but no success

UPDATE  T1
SET    Cap = ((txg*T2.Cap)/100 ),   
        IPi=(case when t2.TPi=1 then ((txg*T2.IPiTT)/100) else T1.IPi end)
FROM   prg T1
       JOIN main T2
         ON T1.id = T2.id
where T1.Cap=0
Thom A
  • 88,727
  • 11
  • 45
  • 75
med
  • 45
  • 5
  • 2
    So what were your attempts? You forgot to include them. Why didn't those attempts work? – Thom A Apr 13 '22 at 10:53
  • And the error you're getting is? – J.Salas Apr 13 '22 at 10:53
  • There is no such thing as "MSSQL". The database engine itself is called [Microsoft] SQL Server, and the dialect it uses is Transact-SQL (T-SQL for short). The query you have above is a T-SQL query, and you want to translate that to an Access Query. – Thom A Apr 13 '22 at 11:06
  • can [this](https://stackoverflow.com/questions/772461/case-expressions-in-access) answer your question? – J.Salas Apr 13 '22 at 11:14

1 Answers1

2

Use IIF in Access SQL:

UPDATE
    T1
SET
    Cap = txg * T2.Cap / 100,   
    IPi = IIF(T2.TPi = 1, txg * T2.IPiTT / 100, T1.IPi)
FROM
    prg AS T1
INNER JOIN 
    main AS T2
    ON T1.id = T2.id
WHERE 
    T1.Cap = 0
Gustav
  • 53,498
  • 7
  • 29
  • 55