HKFuey
asked on
Convert Access formula to SQL
I have this in Access:
60%: IIf(IIf([Months]>19,IIf([M onths]<=25 ,[StkValue ]*0.6))>0, [StkValue] *0.6,0)
Does anyone know the SQL syntax?
In English: if Month number is greater than 19 and less than or equal to 25 then Stock Value multiplied by .6
60%: IIf(IIf([Months]>19,IIf([M
Does anyone know the SQL syntax?
In English: if Month number is greater than 19 and less than or equal to 25 then Stock Value multiplied by .6
Are you using MS ACCESS SQL or SQL Server ?
Hi,
Please try this ...
MS ACCESS
SQL SERVER
Hope it helps!
Please try this ...
MS ACCESS
IIf( [Months] > 19 AND [Months] <= 25 , [StkValue]*0.6 , [StkValue] )
SQL SERVER
CASE WHEN [Months] > 19 AND [Months] <= 25 THEN [StkValue]*0.6 ELSE [StkValue] END
Hope it helps!
ASKER
Hi Pawan, that looks promising. However, I get a date conversion from string error. This is the full formula: -
CASE WHEN DATEDIFF(m , dbo.MyView.LastMoved , '31/12/2016') + 1 > 19 AND DATEDIFF(m , dbo.MyView.LastMoved , '31/12/2016') + 1 <= 25 THEN dbo.InvWarehouse.QtyOnHand * dbo.InvWarehouse.UnitCost * 0.6 ELSE 0 END
CASE WHEN DATEDIFF(m , dbo.MyView.LastMoved , '31/12/2016') + 1 > 19 AND DATEDIFF(m , dbo.MyView.LastMoved , '31/12/2016') + 1 <= 25 THEN dbo.InvWarehouse.QtyOnHand
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.