VBA Question about IsNull

Discussion in 'OT Technology' started by GuiltySparc, Jan 28, 2008.

  1. GuiltySparc

    GuiltySparc OT Supporter

    Joined:
    Apr 14, 2004
    Messages:
    12,070
    Likes Received:
    0
    Location:
    Maryland
    hey dudes, i have a program that passes a ton of values from a database into variables, then does some math on the variables and finnaly spits it out into excel.

    The problem is the database has null values which cause my program to stop. I'm using recordsets to hold the data from the database, so the passing line of code looks like:

    variable = rs!data

    It would be great to be able to write one line of code that does this:

    If any recordset value in the procedure is null, then the variable its being passed to is zero.

    The only thing i can think of is to put an If IsNull()Then zero statement around each variable assignment statement, which is kind of a pain in the ass because the program is pretty huge.

    If IsNull(rs!DAYS) Then
    PPNdays = 0
    Else
    PPNdays = rs!DAYS
    End If

    Any ideas would be greatly appreciated. I have over 100 of these variable assignments so it would be a bit time consuming to throw that IF IsNull in there for all of them. I'm a part time programmer for my department, so sorry if this is a super noobish question :hsd:
     
  2. GuiltySparc

    GuiltySparc OT Supporter

    Joined:
    Apr 14, 2004
    Messages:
    12,070
    Likes Received:
    0
    Location:
    Maryland
    scratch that, i just wrote a query to replace all the null values in a table with 0. Not sure why i didnt think of that to start with.
     
  3. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    1. Store all the data for each recordset in an array.
    2. Iterate through the array, checking for nulls, and if you find one, set the output value = 0 and kill the loop.
     
  4. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Oh hell no, bitch. You posted a question, you're damn well getting a response.
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    put a default value of 0 on your database, if that is the business rule. That way you never have to run that query again
     
  6. GuiltySparc

    GuiltySparc OT Supporter

    Joined:
    Apr 14, 2004
    Messages:
    12,070
    Likes Received:
    0
    Location:
    Maryland
    word, unfortunately i dont maintain the database that the data comes from. I pull in the table from a much larger database (and even then i think its some mainframe extract) and then do a bunch of crap to the data in the table.

    I just added that query to the code that imports the tables each month so its not any extra work to run it.

    Thanks for the responses though :bigthumb:
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    There's also a function called NZ(value,nullvalue) which basically converts any null values in the first argument into the second argument, or leaves it alone if it's not null.

    So if:

    x=5, then nz(x,0) = 5
    x=null, then nz(x,0) = 0
     

Share This Page