CODEWITHSUNDEEP
sql-servervbams-access
jedu
jedu

Reputation: 1341

When to change nz() function to COALESCE function?

I have my Access table linked to SQL server. I have created linked view where I have replaced the nz() function with COALESCE function. However, I am not sure if I have to convert nz function to COALESCE in VBA as well.

For example for the code below:

rst.Open "SELECT * FROM table1", CurrentProject.Connection, adOpenStatic, adLockReadOnly

I have 

IF NZ('Field1',0) =0 then 

//do something

Should I do

IF COALESCE('Field1',0) =0 then 

//do something

Or do I only need to change it in my SQL query?

Upvotes: 1

Views: 745

Answers (2)

ASH
ASH

Reputation: 20342

I'd stick with the NZ() function in Access, and if you want to handle this on the SQL Server side, before pulling it into Access, that's another option. In SQL Server, The COALESCE() function returns the first non-null value in a list.

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

Coalesce is a feature only available in T-SQL it isn't a VBA function.

It's up to you how you want to handle converting nulls into a new value, there are many ways to do this. You can check your variables before adding them to a SQL statement, or, you can alter them as part of the SQL with Coalesce, IsNull or Case.

Personally, I would do whatever is less code that still gets the idea across clearly. I'd imagine this would be decided on a case by case basis depending what it is you are doing.

Upvotes: 1

Related Questions