Open bclothier opened 3 years ago
There's also ??=
, which assigns the RHS if and only if the LHS is null, and ?[]
, which provides a value from an indexed collection if and only if the collection is not null (which would be ?()
in VB-style syntax).
How about including the MS Access Nz function into the VBA library of tB ?
Below function should be 100% compatible. (returning Empty if Value is null)
Public Function Nz(ByRef Value As Variant, Optional ByRef ValueIfNull As Variant = Empty) As Variant
If IsNull(Value) Then Nz = ValueIfNull Else Nz = Value
End Function
Yes, I think that would be a good addition.
How about including the MS Access Nz function into the VBA library of tB ?
Below function should be 100% compatible. (returning Empty if Value is null)
Public Function Nz(ByRef Value As Variant, Optional ByRef ValueIfNull As Variant = Empty) As Variant If IsNull(Value) Then Nz = ValueIfNull Else Nz = Value End Function
But - in backwards compatibility terms - that would not exactly be 100% backwards compatible with the Access NZ() function at present, would it? I mean, in places where Access's NZ() would throw an error, this function may not throw the same errors, would it?
I've been thinking some more about this. There are many ways of representing an absence of something in VBx, many of them relate to Variants:
One massive headache here is when it comes to method invocation. If you accept a Variant that you're expecting (but not sure) contains an object, you have two choices:
Neither are optimal from a boilerplate or performance point of view.
To be clear, this is a problem in many languages. Tony Hoare, the inventor of the null reference, called it his billion dollar mistake (I would argue that, by now and adjusted for inflation, it's many trillions of dollars in 2023).
Describe the solution you'd like
Everything @bclothier noted in the OP. Particularly, a set of robust null operators, of which I'd argue the most important is ?.
. This would mean: "Do I have an object reference? If so, continue to evaluate this line of code. Otherwise, bail out".
As he stated, that would mean that it's safe to write this:
SomeDate = Forms("SomeForm")?.Controls("SomeControl")?.Value
without checking two different points for failures, or accepting and handling the failure as an error.
Note, this doesn't protect from a situation where the wrong type of object is supplied. But in practice, that's going to be far less likely, and thus fine to treat as a runtime error. As a C# developer, I see Variant
as roughly equivalent to dynamic
in C#.
Is your feature request related to a problem? Please describe. This is a issue that's dear to me, probably because I work with Access & SQL Server and am always annoyed that I don't have a proper nullable type system to work with and find it clumsy to work with nulls.
Variant
is the only data type capable of storing aNull
value but then it goes on to muddy the water with storing any other data types, which is contrary to how database engines expect their data to be. Therefore, this code is dangerous:The
SomeBoundControl
is surely bound to a column that can only contain a date. Yet that same column could potentially contain aNull
. TheValue
property of an Access control is aVariant
data type. Thus I could do:What utter nonsense! And this also can be a potential runtime error or worse will just coerce to some valid value which is almost never what one wants.
Within Access, we can guard against nulls using
Nz()
function orIsNull()
function:But because it's
Variant
, this becomes woefully inadequate:Nothing's stopping the user from typing in an empty string or nonsense like "Feb 31 2021". Therefore, it's common to guard against invalid inputs by something like the following:
For
Date
data:For
String
data:For any integer data (may be too strict):
Or (may be too loose)
All those are horrid. Why must we go through the mental gymnastic of checking that a
Variant
is a proper data type and otherwise convert it into the desired data type? The default behavior of raising an error is also horrid because now we have error-as-the-control which is also a bad coding pattern to have.Describe the solution you'd like We need a way of properly constraining the allowable values. As an example, I should be able to do this:
The
Date?
would indicate that it can contain any validDate
value ORNull
. Not strings. Not integers. Not floats. Not some hairball from your crazy aunt Bea's even crazier cat.I'm not sure how this could be implemented. If we want a full compatibility with VB* clients, the tB compiler could emit additional instructions to do a safe coercion of the invalid value into
Null
so that theVariant
will only be of eitherVT_NULL
orVT_DATE
in this situation. That results in less cluttered code.However, that may end up doing too much and possibly violating the principle of least astonishment. Alternatively the tB compiler could emit instruction to raise an error if it cannot fit either
VT_NULL
orVT_DATE
so there's no surprises when garbage input is given andNull
is gotten back.Yet another approach is to treat this as a compile-time inspection only. There would be nothing special for the VB* consumers but for tB code, it would be easy to find suspicious casts and then mark them to indicate that they should only contain a certain data type or
Null
.Also, there is a complementary approach to borrow from .NET (mainly C#). We can do this instead:
The
?.
operator indicates that if the object is null, theValue
should returnnull
as well, which helps to prevent runtime error when the formSomeForm
or the controlSomeControl
are not open or otherwise available for consumption, thus preventing a runtime error. That eliminates the need for anOn Error Resume Next
+On Error GoTo 0
block around the dangerous access, which again helps with the code readability.And there's also this operator:
which indicates that if it's
Null
(or maybe invalid value, too?),Date()
is provided as the default. This is like Access'Nz()
function but that wouldn't require everyone to reference the Access object library just to have that functionality.Those 2 operators can be used in conjunction with the nullable types which would then immensely simplify the process in handling values without having to get into ugly mechanics of handling each data type differently.