I am working on an MS Access to SQL Server Migration project and am currently in the process of converting a complex query into T-SQL from MS Access. Now I’m quite familiar with how Nz works in Access and how a 0 or empty string ” “ is returned if the valueifnull argument is not provided. Source: https://support.office.com/en-gb/article/nz-function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c
The exception to this rule is if the Nz function is used within a query expression, in which case, the returned value in the event of a null in the Variant, is an empty string.
Now, moving onto my actual problem, I am working on converting this Nz-filled query expression into T-SQL using ISNULL in T-SQL. ISNULL requires 2 arguments. Both the Expression and the Value. The latter being an optional argument in Access’ Nz, has made it a bit difficult for me to translate the expression. The Value argument also needs to match the original data type of the Variant (in SQL Server), this means that I can not just simply add an empty string ” “ as the second argument as Nz in Access does by default.
Take a snippet of the complex query I use in Access compared to what I’ve written in T-SQL:
Access
TotalWIP: IIf([PercentageDoneTotal]<0,0,IIf(nz([TotalPurchasesReceived])+ (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0))+IIf((([Accepted Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))>0,((([Accepted Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))* [PercentageDoneTotal]),0)>([Accepted Price]*[OutstandingBalance]*0.9), ([Accepted Price]*[OutstandingBalance]*0.9),nz([TotalPurchasesReceived])+ (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0))+IIf((([Accepted Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))>0,((([Accepted Price]*[OutstandingBalance]*0.9)-nz([TotalPurchasesReceived])- (IIf([PercentageDoneTotal]>0,nz([TotalStockAllocated]),0)))* [PercentageDoneTotal]),0)))T-SQL
IIf([PercentageDoneTotal]<0,0,IIf(ISNULL([TotalPurchasesReceived],NULL)+ (IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0))+IIf((([Accepted Price]*[OutstandingBalance]*0.9)- ISNULL([TotalPurchasesReceived],NULL)-(IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))>0, ((([Accepted Price]*[OutstandingBalance]*0.9)- ISNULL([TotalPurchasesReceived],NULL)- (IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))* [PercentageDoneTotal]),0)>([Accepted Price]*[OutstandingBalance]*0.9), ([Accepted Price]* [OutstandingBalance]*0.9),ISNULL([TotalPurchasesReceived],NULL)+ (IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0))+ IIf((([Accepted Price]*[OutstandingBalance]*0.9)- ISNULL([TotalPurchasesReceived],NULL)- (IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))>0, ((([Accepted Price]*[OutstandingBalance]*0.9)- ISNULL([TotalPurchasesReceived],NULL)- (IIf([PercentageDoneTotal]>0,ISNULL([TotalStockAllocated],NULL),0)))* [PercentageDoneTotal]),0)) ) AS TotalWIPNote the above NULL in the T-SQL example. This NULL is not suitable as it is messing up my calculations, I essentially need to mimic Nz without a second argument in T-SQL.
Edit: Without putting the entire query into this question, Here is an expected result for Job Number: 294784 in both MS Access and SQL Server:
Access Results:
SQL Results:
Edit 2: have added the edited to add entire code.
Advertisement
Answer
As @Salman A noted, this is a great reason not to use variant. But having said that, I assume that you are looking for a numeric value, so why not use:
IIF([PercentageDoneTotal]<0,0, IIF(ISNULL([TotalPurchasesReceived],0)+ (IIF([PercentageDoneTotal]>0,ISNULL([TotalStockAllocate],0),0))