2 Replies Latest reply: May 15, 2014 10:16 AM by AlexM _ RSS

    Very odd result with Calculated Field

    AlexM _

      I'm completely stumped by a calculated field which is basically looking at two sets of Access title data and, where one record is blank, it should simply substitute it with data from another:


      Upper(If(<>"",[Author (new)],Author))


      As far as I can tell, there's nothing wrong with the underlying data - although I'm going to investigate further - but I'm getting completely different records popping up. Why, when a record is NOT blank, would it display a completely different record that's not in the dataset?


      I can only think that an IsEmpty( ) function might be better. Thoughts anyone?


      Note: Since the same result happens in both 10.5 and 11.8, I can only suspect some oddity in the function or data.

        • Very odd result with Calculated Field
          Olly Bond

          Hello Alex,


          If you're working with databases, what's the setting for handling null input strings? Is it checked to replace null strings with ""?


          Best wishes,



            • Very odd result with Calculated Field
              AlexM _

              It's blank (assuming we're talking about the View option) but I've now discovered something more worrying. The result appears to be correct using v10.5 on one computer but incorrect using v11.8 on another when both share exactly the same model, project and underlying data - which has incidentally been refreshed!


              Needless to say, this opens up a few cans of worms since, if there's no obvious logic, how can one trust the results of v11.8? My only other suspicion is that perhaps there's some other installation on the computer itself which is at work here so I'm going through a few of those options right now. I'd be curious if anyone else has encountered this problem though.


              UPDATE: In the absence of anything obvious to explain the difference - even a reboot - I simply deleted the Calculated Field and the External Lookup then reconnected the link and rebuilt the Calculated Field again in v11.8 and all now looks fine. How does one explain that!?