What the Blank is Going On?

Sooner or later, all Access developers learn about Null, Zero Length String (ZLS) and even the sneaky Blank that sometimes makes its appearance in Access.

I am going to assume that you, gentle reader, already know all about Nulls and ZLS's, so I won't go off-track. If you do need a refresher, here's a good, if a bit lengthy, discussion.

Anyway, I don't feel too bad when I occasionally get tripped by PowerApps "Blank()", which acts sometimes like Null, and sometimes like something else. I just shrug my shoulders and remind myself not to trust Blank() too much.

The other day, though, I started getting weird results from a stored procedure executing in a PowerApps app, even after I thought I'd set up a trap to handle the occasional Blank(), or Null.

Here's the orginal code and an explanation of what it was, theoretically, doing.

Set(
   gdtDateReceived,
   If(
       IsBlank(Text(inpDateReceivedRcvdDtDel.Text)) || Text(inpDateReceivedRcvdDtDel.Text) = "1900-01-01",
       Blank(),
       Coalesce(
           Text(inpDateReceivedRcvdDtDel.Text),
           Blank()
       )
   )
);
Set(
   grecUpdatedBook,
   UpdateBookDateReceived.dbouspUpdateBookDateReceived(
       {
           PublicationID: grecBook.PublicationID,
           DateReceived:  DateValue(
                   Substitute(
                       gdtDateReceived,
                       "/",
                       "/01/"
                   )
               )
           )
       }
 
);

The operation captures the date a book was added to the library's catalog. It's a bit tricky, because the librarian writes the date received as "mm/yyyy" on the first page of the book. That way, she can rotate out the oldest books as new donations come iin.

However, to store the date received more efficiently as a date in the SQL Server table, I wrote the PowerApps formula to convert the string"mm/yyyy" to the string "mm/01/yyyy" and then capture the DateValue of that expression for the SQL Server Stored procedure as unambiguously a null or a valid date -- at least that is what I thought I'd done. That's handled in the argument that sets up DateReceived as a parameter for the stored proc. I kind of did stuff like that in Access for years. And didn't really think too much about it, at first.

                    DateReceived:  DateValue(
                    Substitute(
                        gdtDateReceived,
                        "/",
                        "/01/"
                    )
                )

There's a subtle flaw in the PowerFX formula, though, that'll elaborate on below.

Sometimes, when scanning books for the catalog, we run across books without a DateReceived written on the front page and I have to pass a null through the Stored procedure for the DateReceived.

It all works, most of the time.

The first Set() command is supposed to create either a validly formatted "mm/yyyy" string or a Blank() for the stored proc.

Note, however, that safety check for "1900-01-01". That helps prevent Zero Length Strings (as opposed to nulls or valid dates) from making their way into the Stored Procedure where they can be interpreted as something other than Null or a date. In early testing, in fact, the result could be a non-null, non-date value that ended up being handled as the epoch date for SQL Server, "1900-01-01". I started trapping for it.

How all that works is a parallel branch that I won't pursue. Trust me, though when I say that unexpected conversion did happen, more than once.

The real problem, though, is even more subtle. In PowerApps, the DateValue() conversion doesn't do well with ambiguous strings. And apparently, it was getting some ambiguous strings from gdtDateReceived in some edge cases. SQL Server ended up storing the epoch date, "1900-01-01", not a null, when I wanted it to store the null. My guess was that DateValue() didn't know what to do with the strings it got sometimes.

The solution, worked out with my AI Assistant, Claude Chat, was to explicitly send either a Blank or a valid Date through the Stored Proc.

//Added Guard for explicit Blank() in Date Received
Set(
    grecUpdatedBook,
    UpdateBookDateReceived.dbouspUpdateBookDateReceived(
        {
            PublicationID: grecBook.PublicationID,
            DateReceived: If(
                IsBlank(gdtDateReceived),
                Blank(),
                DateValue(
                    Substitute(
                        gdtDateReceived,
                        "/",
                        "/01/"
                    )
                )
            )
        }
    )
);

Here's the guard clause.

            DateReceived: If(
                IsBlank(gdtDateReceived),
                Blank(),
                 ...

To an old Access developer, that probably seems a bit redundant. However, what it does is unambiguously send a Null, i.e. Blank(), whenever the gdtDateReceived is not a properly constructed and valid date.

SQL Server is happy, PowerApps is happy. I'm happy. The librarian is, I hope, happy.