Solved: Using SQL to translate Nulls into Empty Strings

[Dr.K]

My SQL skills are kinda weak... please be kind.

Currently, I use "null scrubber" function to Null fields into empty strings, becuase VBA will choke if its expecting a string and it gets a Null.

[vba]Function Null2Empty(strField) As String

If IsNull(strField) = True Then
Null2Empty = Empty
Else
Null2Empty = strField
End If

End Function[/vba]

However, I pretty sure that there are ways of translating Nulls into Empty Strings right in the SQL statement, I just don't know how.

Any advice is apppreciated.

[XLGibbs]

SQL has an isNull function..

IsNull(ColumnName,value) as ColumnName

so the value would replace anything that would be null.

A better option would be to have the source data NOT accept nulls in that column and make the default value an empty string.

[Dr.K]

Ah thank you, thats a good start!

A better option would be to have the source data NOT accept nulls in that column and make the default value an empty string.

Well, I'm pulling from two DBs: I have no control over whats in the Data Mart, so I'll have to use the SQL function you showed me.

As for the Access DB, how do I set that up?
Required = true
Allow zero length = true

Will that do it?

Thanks again.

[Dr.K]

Actually, IsNull doesn't work on DB2. Since my Data Mart is on a DB2 server, I had to use COALESCE instead of IsNull.

I googled around and found a DB2 SQL reference on the IBM website, this cleared up the problem for me. This is what my usage looks like:


COALESCE(MSE_TCKR_SYMB, '') AS MSE_TCKR_SYMB


Thanks for the guidance!