Conversion Access Query to SQL Server 2000

SELECT DISTINCTROW OILPALM.NAME, formatyear([year]) AS yearDisplay, OILPALM.YEAR, OILPALM.UNIT, OILPALM.BLOCK, OILPALM.BlockInYield, [Y_01_total_ton]+[Y_02_total_ton]+[Y_03_total_ton]+[Y_04_total_ton]+[Y_05_total_ton]+[Y_06_total_ton]+[Y_07_total_ton]+[Y_08_total_ton]+[Y_09_total_ton]+[Y_10_total_ton]+[Y_11_total_ton]+[Y_12_total_ton] AS Production, IIf([m_a]>0,[Production]/[m_a],0) AS [Avg Yield], ([Y_TH_POT]*IIf([blockinyield]=True,[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100) AS TotalTon_Pot, IIf(IIf([blockinyield]=True,[SIZE],0)>0,([Y_TH_POT]*IIf([oilpalm].[blockinyield]=True,[oilpalm].[size],0))*(([YieldP_01]+[YieldP_02]+[YieldP_03]+[YieldP_04]+[YieldP_05]+[YieldP_06]+[YieldP_07]+[YieldP_08]+[YieldP_09]+[YieldP_10]+[YieldP_11]+[YieldP_12])/100)/IIf([blockinyield]=True,[SIZE],0),0) AS [Yield pot], [Avg Yield]-[Yield pot] AS [Yield Gap], OILPALM.Y_KGB AS [Avg bunchweight], IIf([TotalTree]>0,[Total bunches]/[TotalTree],0) AS [Avg Bunch No], OILPALM.SIZE AS [Total size], OILPALM.Y_TOTAL_mandays AS [Total workforce], OILPALM.Y_AVG_harvest_int AS Avg_Harvest_int, OILPALM.Y_AVG_harvest_int AS sum_Harvest_int_sum, IIf([Y_AVG_harvest_int]>0,1,0) AS rec_Harvest_int, IIf([rec_Harvest_int]>0,[sum_Harvest_int_sum]/[rec_Harvest_int],0) AS Calc_Harvest_int, OILPALM.Y_per_manday AS Avg_YMD, [Y_01_TOTAL_bunches]+[Y_02_TOTAL_bunches]+[Y_03_TOTAL_bunches]+[Y_04_TOTAL_bunches]+[Y_05_TOTAL_bunches]+[Y_06_TOTAL_bunches]+[Y_07_TOTAL_bunches]+[Y_08_TOTAL_bunches]+[Y_09_TOTAL_bunches]+[Y_10_TOTAL_bunches]+[Y_11_TOTAL_bunches]+[Y_12_TOTAL_bunches] AS [Total bunches], OILPALM.Y_TOTAL_mandays AS [Total manday], IIf([Blockinyield]=True,IIf([TC_M]>0,[TC_M],[TC_DENS]*[size]),0) AS TotalTree, OILPALM.TC_M, IIf([TotalTree]>0,([Production]*1000)/[TotalTree],0) AS [Avg bunchweighMTree], IIf([Total workforce]>0,[Production]/[Total workforce],0) AS TMd, (IIf(([oilpalm]![blockinyield]=-1),[OILPALM]![SIZE],0)) AS M_A
FROM OILPALM INNER JOIN SYS_soil_type ON OILPALM.SOILTYPE = SYS_soil_type.SoilType
ORDER BY OILPALM.YEAR DESC , OILPALM.UNIT, OILPALM.BLOCK;

Please Help Me....

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||

1. Change all your IIF(CONDITION,TRUE STMT, FALSE STMT)

CASE WHEN CONDITION THEN TRUE STMT ELSE FALSE STMT END

2. You need to convert your formatyear macro logic to SQL Server