1

I have a column_a with value, but occasionally it is empty (i.e. NULL), I want to use SQL (Microsoft Access 2003) to access this column and return the column_a value, but it the field does not have a value, I want to pad it with a '0'. The code that I am running is:

SQL_string = "SELECT ID, (IF IsNull(column_a) THEN 0, ELSE column_a END IF) ... FROM everything"

How can I do it properly?

shA.t
  • 16,580
  • 5
  • 54
  • 111
pckong
  • 21
  • 3

2 Answers2

0

You want the nz() function:

select id, nz(column_a, 0)
from . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have tried as you suggested, but NZ function is not available in MS access 2003, it ends up a "NZ" invalid identifier. – pckong Apr 17 '15 at 03:40
  • 1
    @pckong Seems you're executing the query from outside MS Acess interface? see comment: http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql#comment231738_247869 – har07 Apr 17 '15 at 04:20
0

If Nz() does not work for you then you can use IIf() along with the proper form of IsNull() for Access SQL, which is

SQL_string = "SELECT ID, IIf(IsNull(column_a), 0, column_a) AS whatever ... FROM everything"
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • The IFF() function does not work as well. I am running the code on MS Access 2003. It comes out an invalid identifier error. I think it is more of SQL version issue. But thanks for your comments. – pckong Apr 19 '15 at 23:35
  • `Iff()` is not valid. The function name is `Iif()`. – Gord Thompson Apr 20 '15 at 00:30