Skip to content
Advertisement

NZ function in T-SQL where ValueIfNull is NOT specified

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

T-SQL

Note 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: enter image description here

SQL Results: enter image description here

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:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement