Link to home
Start Free TrialLog in
Avatar of JMO9966
JMO9966

asked on

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 ??


Thanks,
JMO9966
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

select Nz(Terms, "") As Terms from Customer
Avatar of JMO9966
JMO9966

ASKER

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 vb.net app that is using an Odbc.OdbcConnection.

I get an error about an unrecognized function ??

Any thoughts would be appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JMO9966

ASKER

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

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JMO9966

ASKER

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
Avatar of JMO9966

ASKER

IsNull errors out too on this Access database.  
what is the error in .NET using ODBC?
Avatar of JMO9966

ASKER

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.