1 Reply Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Calculation with NULL-Fields

    Eumel _

      I'm opening a txt-file by "Opening a database.

      I have some calculated fields. There I have the problem, that if I add two fields and one of them is a NULL-Field (even if I changed it into "0") there will be no result. At the moment I fixed the problem  by using if(). But this is not realy comfortable. Is there any function like the MS Access-Function NZ()

        • Calculation with NULL-Fields
          Grant Perkins

          Hi Eumel and welcome to the forum.

           

          The option to display null as 0 is just for display purposes and does not change the underlying data. In a database situation changing the underlying data would be wrong even if possible.

           

          You need to use a calculated field probably uysing some combination of the ISNULL() and IF() functions and then perform the calculations with that field. You may wish to hide the calculated field from any outputs.

           

          It sounds like you are already doing that but you feel uncomfortable with it. Is there any reason to feel uncomfortable with the formula?

           

          I think if you could see into the Access function you would find that the code behind the function was doing much the same thing as Monarch does using the ISNULL and IF functions but that Access presents it as a single function and hides the 'field' that performs the calculation.

           

          I hope this helps in some way.

           

           

          Grant