MS Access - how to convert null values to empty string in select statement ?

I've used this method in sql to convert null value to empty string but I'm looking for a hand in how to do the same with MS Access select query:

sql syntax:

select IsNull(Terms,'') As Terms from Customer

Access syntax ??

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Sorry, not a .net guy :)
Patrick MatthewsCommented:
select Nz(Terms, "") As Terms from Customer
JMO9966Author Commented:
Thanks, your syntax is good and now I'm remembering I've struggled with this in the past also.

The query runs fine in Microsoft Access but I can't get this query to run from my app that is using an Odbc.OdbcConnection.

I get an error about an unrecognized function ??

Any thoughts would be appreciated.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

JMO9966Author Commented:
Is there a way to cast this to varchar in Access query and work around this ?

cottsakConnect With a Mentor Commented:
Nz() only works within Access and is not valid thru Odbc.OdbcConnection. try IsNull() again or perhaps handle the null values in VB.NET after you have your recordset/dataset returned from the query.
JMO9966Author Commented:
Ya, I know I've worked around this before.

I thought about doing this in code, but my select query blows up even with simple select because some records have a null value for this field.

I need to use an ODBC connection to use an existing DSN database pointer.

Thanks though
JMO9966Author Commented:
IsNull errors out too on this Access database.  
what is the error in .NET using ODBC?
JMO9966Author Commented:
straight select statement = "Conversion from type DBNull to String is not valid"

select with nz and isnull attempts = "unrecognized command or wrong number of arguments"  

I ended up creating a datatable and filling this with selectCommand versus a odbc command with ExecuteScalar and now the datatable gets filled with null values where they exist and then I convert these to empty strings in the datatable before gathering value.

Thanks guys
no problems. thanx for the points and grade.
happy coding.
I know this question is closed but I usually use this:
select Terms & "" As TermsFixed from Customer
This usually handles it for me, and I haven't had any problems, but I don't know what the rest of you think about it.
Also, I don't know if it will work with ODBC or .NET.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.