CASE in SQL

[alka]

I have 3 column



Column 1 from another queryA has the field ValueA
Column 2 from another queryB has the field ValueB
and I want my 3rd column to be: =[queryA].[ValueA]-[queryB].[ValueB]


So put in field of colum3: ValueColumn3: [queryA].[ValueA]-[queryB].[ValueB]

But now I want that if the ValueColumn3 is negative, I want it to show 0 else if it is >0, show the calculated value.

I have tried several CASE but everytime I get a synthax error.

Tried something like this:


CASE
WHEN ([queryA].[ValueA]-[queryB].[ValueB]) BETWEEN -100000000 AND 0 THEN 0
ELSE
([queryA].[ValueA]-[queryB].[ValueB]])
END As ValueColumn3




There must be a better way to do it and I cant get any case to work

[pbaldy]

Try

CASE
WHEN [queryA].[ValueA]-[queryB].[ValueB] < 0 THEN 0
ELSE
[queryA].[ValueA]-[queryB].[ValueB]
END As ValueColumn3

[alka]

Still get the same error (you can see that the code i use is different but I wanted you to understand)

[pbaldy]

Is this an Access query or SQL Server? Case doesn't work in Access.

[alka]

Oh I didn't know that! It is a Access query :S

[alka]

I should do my case in a event code? or is there another way to do this in SQL Query Access?

[pbaldy]

You can use an IIf() function in place of the Case function.

[alka]

IFF(
-([inventaire].[lb_qte_recu]-[Besoin travail].[lbt_qte_necessaire])< 0 THEN 0
ELSE
-([inventaire].[lb_qte_recu]-[Besoin travail].[lbt_qte_necessaire])
END) As Lb_qte_demande


Same error

[pbaldy]

Check in help for the proper syntax.

[alka]

http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx


<Message> in query expression <expression>. (Error 3075)

[pbaldy]

IIf(Criteria, TrueResult, FalseResult)

[alka]

WOW YAY! Thanks man!

[pbaldy]

Happy to help!