Nightmare query upgrade from Access

Hi, nice to meet you all ^^

I've been given this access database to look at which could eventually be upgraded to SQL, I am conducting a kind of initital research in to how we would go about it. The previous system has developed over time and is pretty messy. I'm not really a database developer, but as everyone is busy on other things, this side project has been given to me to look at as a warm up!

Basically I've managed to upsize all the tables and their contents, and have begun copying the forms over to a .adp - creating most of the queries as stored procedures.

I'm working through cronologically, and this is the 2nd query the system requires... It was full of IIf statements which I have commented out. The adp works, but is missing a lot of data - can't really work out where it was meant to be feeding from, I personally can't stand access - but it's what the original was created in and what they want to use.

ALTER PROC [qrySaleLotEntry] @.saleid nvarchar(3)
AS
SELECT [Sale & Lot].SaleNum, [Sale Details].SaleDate, [Sale Details].SaleDesc, [Sale & Lot].LotNum,
[Sale & Lot].onsite, [Sale & Lot].StockNum, [Sale & Lot].ThisEntryFee, [Sale & Lot].EntryFeePaid,
Accounts.[4x4ENTRYFEE], Accounts.CARENTRYFEE, [Sale & Lot].Vendor, Accounts.VENDORDESC, Accounts.ACCOUNTNAME,
Accounts.[GROUP], Accounts.CONTACT, Accounts.PHONE, Accounts.MOBILPHONE, Accounts.VATNO, Accounts.[e-mail],
[Sale & Lot].RegYear, [Sale & Lot].RegLetter, [Sale & Lot].RegDate, [Sale & Lot].RegNum, [Sale & Lot].ManuYear,
[Sale & Lot].Manufacturer, [Sale & Lot].Model, [Sale & Lot].[Type], [Sale & Lot].CAPCode, [Sale & Lot].Colour,
[Sale & Lot].MileageNum, [Sale & Lot].Kilometres, [Sale & Lot].Warranted, [Sale & Lot].ServiceHistory,
[Sale & Lot].ServiceNum, [Sale & Lot].ServiceMileage, [Sale & Lot].ServiceDate, [Sale & Lot].MoTDay, [Sale & Lot].MoT,
[Sale & Lot].Tax, [Sale & Lot].ReservePrice, [Sale & Lot].VATCode, [Sale & Lot].CAPValueClean, [Sale & Lot].CAPValueAve,
[Sale & Lot].CAPValuePoor, [Sale & Lot].AgrNum, [Sale & Lot].Extra1, [Sale & Lot].Extra2, [Sale & Lot].Extra3,
[Sale & Lot].Extra4, [Sale & Lot].Extra5, [Sale & Lot].Extra6, [Sale & Lot].Extra7, [Sale & Lot].Extra8,
[Sale & Lot].Extra9, [Sale & Lot].Extra10, [Sale & Lot].ASSEEN, Accounts.ASSEEN, Accounts.UNWARRANTED,
[Sale & Lot].NoMMF, [Sale & Lot].UNROADWORTHY, [Sale & Lot].PRESALEHPI, [Sale & Lot].[COLLECTION],
Accounts.[CollectionRequired?], [Sale & Lot].TotalLoss, [Sale & Lot].Accident, [Sale & Lot].FinanceOwed,
[Sale & Lot].Taxi, [Sale & Lot].V5, [Sale & Lot].V5Part2, [Sale & Lot].PlateTfr, [Sale & Lot].PlateTfrCharge,
Accounts.PlateTfrCharge, [Sale & Lot].PlateTfrPaid, [Sale & Lot].Fuel, [Sale & Lot].FuelCharge, Accounts.FuelCharge,
[Sale & Lot].PreSalePrep, [Sale & Lot].PreSalePrepCharge, Accounts.PreSalePrepCharge, [Sale & Lot].Misc1,
[Sale & Lot].Misc1Desc, [Sale & Lot].Misc1Charge, [Sale & Lot].Misc2, [Sale & Lot].Misc2Desc, [Sale & Lot].Misc2Charge,
[Sale & Lot].DeliveryIn, [Sale & Lot].DeliveryInCharge, Accounts.DeliveryInCharge, [Sale & Lot].DeliveryOut,
[Sale & Lot].DeliveryOutCharge, Accounts.DeliveryOutCharge, [Sale & Lot].WashOff, [Sale & Lot].WashOffCharge,
Accounts.WashOffCharge, [Sale & Lot].Polish, [Sale & Lot].PolishCharge, Accounts.PolishCharge, [Sale & Lot].ValetA,
[Sale & Lot].ValetACharge, Accounts.ValetACharge, [Sale & Lot].ValetB, [Sale & Lot].ValetBCharge,
Accounts.ValetBCharge, [Sale & Lot].ValetC, [Sale & Lot].ValetCCharge, Accounts.ValetCCharge, [Sale & Lot].Delogo,
[Sale & Lot].DelogoCharge, Accounts.DelogoCharge, [Sale & Lot].ENGINEER, [Sale & Lot].EngineersCharge,
Accounts.EngineersCharge, [Sale & Lot].Repairs, [Sale & Lot].RepairsCharge, Accounts.RepairsCharge,
[Sale & Lot].Provisional, [Sale & Lot].Purchaser, Accounts_1.ACCOUNTNAME, Accounts_1.CONTACT, Accounts_1.PHONE,
Accounts_1.MOBILPHONE, Accounts_1.[e-mail], [Sale & Lot].SalePrice, [Sale & Lot].Nett, [Sale & Lot].VAT,
[Sale & Lot].VATDesc, [Sale & Lot].TransInbound, [Sale & Lot].TransInboundPaid, [Sale & Lot].TransReturn,
[Sale & Lot].TransReturnPaid, [Sale & Lot].[Transfer Complete], [Sale & Lot].datetostock, [Sale & Lot].Reentry,
[Sale & Lot].ChassisNum, [Sale & Lot].Comments, [Sale & Lot].PackNumber, [Sale & Lot].MissedDeadline,
[Sale & Lot].Created, [Sale & Lot].CreatedBy, [Sale & Lot].Modified, [Sale & Lot].ModifiedBy, Accounts.TradeStatus,
[Sale & Lot].VIN, [Sale & Lot].PrevVRM, [Sale & Lot].DateVRMChanged, [Sale & Lot].ExpModel, [Sale & Lot].EngineNum,
[Sale & Lot].OrigCol, [Sale & Lot].NumPrevCols, [Sale & Lot].DateLastColChange, [Sale & Lot].PrevCol,
[Sale & Lot].AgreementType, [Sale & Lot].AgreementTerm, [Sale & Lot].AgreementDate, [Sale & Lot].FinanceCo,
[Sale & Lot].FinanceTel, [Sale & Lot].FinanceAgrNum, [Sale & Lot].FinanceDesc, [Sale & Lot].PoliceForce,
[Sale & Lot].PoliceTel, [Sale & Lot].PoliceReportDate, [Sale & Lot].RiskCompany, [Sale & Lot].RiskTel,
[Sale & Lot].RiskRef, [Sale & Lot].RiskPeriod, [Sale & Lot].RiskInterestDate, [Sale & Lot].RiskType,
[Sale & Lot].RiskOther, [Sale & Lot].ConditionInsurer, [Sale & Lot].ConditionClaimNum, [Sale & Lot].ConditionTel,
[Sale & Lot].ConditionMIAFTRDate, [Sale & Lot].ConditionMake, [Sale & Lot].ConditionModel, [Sale & Lot].KeeperPrevNum,
[Sale & Lot].KeeperChangeDate, [Sale & Lot].KeeperDateAcquired, [Sale & Lot].KeeperDateDisposed, [Sale & Lot].Scrapped, [Sale & Lot].Exported
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
WHERE dbo.[Sale & Lot].SaleNum = @.saleid
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;

/*
IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,
IIf([Accounts.VENDORDESC] Not Like "","Direct from") AS DirectFrom, [Sale & Lot.Manufacturer] & " " & [Sale & Lot.Model] & " " & [Sale & Lot.Type] AS [Desc], StrConv([sale & lot.RegNum],1) AS Reg,
IIf([accounts.unwarranted]=-1,"Unwarranted - Company Policy",
IIf([sale & lot.warranted] Like "1",[sale & lot.MileageNum] & " " & IIf([sale & lot.kilometres]=-1,"kms ") & "Warranted",
IIf([sale & lot.warranted] Like "2","Unwarranted",IIf([sale & lot.warranted] Like "3","Incorrect")))) AS MileageStatus,
IIf([Sale & Lot.VATCode] Like 1,"Plus VAT",
IIf([Sale & Lot.VATCode] Like 2,"No VAT",
IIf([Sale & Lot.VATCode] Like 3,"Inc VAT"))) AS VATStatus,
IIf([sale & lot.v5]=-1,"V5 here",IIf([sale & lot.v5part2]=-1,"V5/2 here",IIf([sale & lot.v5]=0 And [sale & lot.v5part2]=0,"V5 not here"))) AS V5here, IIf([Sale & Lot.Tax] Is Not Null,"Tax " & [Sale & Lot.Tax],"No Tax") AS Taxhere, IIf([sale & lot.MoT] Is Not Null,"MoT " & [sale & lot.MoTDay] & " " & [sale & lot.MoT],"No MoT") AS MoThere, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No Service History",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Service History",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS Services, IIf([Sale & Lot.ASSEEN]=-1,"As Seen",IIf([Sale & Lot.NoMMF]=-1,"No Major Mechanical Faults",IIf([Sale & Lot.UNROADWORTHY]=-1,"Unroadworthy"))) AS MechDesc, IIf([Sale & Lot.PrevLotNum]>0,[Sale & Lot.PrevSaleNum] & "/" & [Sale & Lot.PrevLotNum],"") AS Prev, IIf([sale & lot.MoT] Is Not Null,Trim([sale & lot.MoTDay] & " " & [sale & lot.MoT]),"No") AS PendragonMoT, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Yes",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS PendragonServiceHist, IIf([Sale & Lot.ServiceMileage] Is Null,"","(Last Serviced " & [Sale & Lot.ServiceMileage] & ", " & [Sale & Lot.ServiceDate] & ")") AS LastServiced, IIf([Sale & Lot.Tax] Is Not Null,[Sale & Lot.Tax],"No") AS PendragonTax, Trim([Sale & Lot]![Extra2] & [Sale & Lot]![Extra3] & [Sale & Lot]![Extra4] & [Sale & Lot]![Extra5] & [Sale & Lot]![Extra6] & [Sale & Lot]![Extra7] & [Sale & Lot]![Extra8] & [Sale & Lot]![Extra9] & [Sale & Lot]![Extra10] & IIf([Sale & Lot]![TotalLoss]=-1," Total Loss") & IIf([Sale & Lot]![Accident]="No",""," " & [Sale & Lot.Accident]) & IIf([Sale & Lot]![Taxi]="No",""," " & [Sale & Lot.Taxi])) AS PendragonExtras, IIf([sale & lot.v5]=-1,"YES","NO") AS LogBook, IIf([sale & lot.Extra2] Like " Service History,","YES","NO") AS ServiceHistCondRep
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;

*/

I've commented out the IIf statements. I began by trying to convert them in to CASE statements and such - but frankly can't get my head around that.

Getting back to basics... I suppose the question would be: I am upsizing from access to SQL, what the heck do I do with all these IIf statements?IS your question how to change IIF(Experssion,true,false) to a case statement

iif(1=2,'One=Two','ONE<>TWO')

would return 'ONE<>TWO'

case when 1=2 then 'One=Two' else 'ONE<>TWO' end

would return 'ONE<>TWO'

Also

IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,

CASE WHEN SALEDESC like 'C%' then lotnum + 'C' else lotnum + 'F' end|||Thanks - that's a starting step for me!