Results 1 to 4 of 4

Thread: Null with Zeros

  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Question Unanswered: Null with Zeros

    How do I replace null values or blanks in my query with zeros?
    Field name: 2004
    I tried =iif(isnull([2004]),0,[2004]) in the criteria box, but when I ran the query, only a third of the record showed up. What am I doing wrong?

    Thanks in advance

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nz(thisfield, 0)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by izyrider
    nz(thisfield, 0)

    izy
    I tried that. Now I got no records at all.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Ahhhhh yes, i see now you are talking about the criteria being null, not the data in the table.

    i do this crap in dynamic SQL with my own form so i use rudy's famous 1=1 and it goes something like this

    strSQL = "SELECT blah FROM myTable WHERE 1=1 "
    if not isnull(myFormsCriteriaBox) then
    strSQL = strSQL & "someField = " & myFormsCriteriaBox
    endif
    strSQL = strSQL & " ORDER BY otherField;"

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •