If all table fields were variants, then the table size would NOT change when you change the datatypes of fields in the table ...
>> Can a "proper" yes/no field ever be null? Yes, before the first time its freshly instantiated record is first written. <<
Nope ... did you see my recordset code earlier in the thread (Post #13)? ... after the .AddNew (ie: a newly instantiated, uncommitted record) a Yes/No field is False ... even if a default value is not specified ...
One field does not cut it because of data structures and I don't know what else, but one field does not reveal the difference .... so try this ...
Same two data bases with 8 fields ...
The Date/Times: 9,220,096 bytes
The Booleans: 2,240,512 bytes
Definately of different size ... so its doubtful all fields are Variants as we know them in VBA.
-----
Do the code samples I have provided through out this thread (escpecially in post #15) not verify the claims I have made? ... Does my code not verify that in your samples, you were working with a CONTROL, which can indeed be Null, as appossed to a Yes/No FIELD which can not be Null? ... The code verifies that when working through the Form object in the manner you have, you're working with a CONTROL and not directly with the FIELD. Its simple really, a Form object does not even have a Fields collection, but it has a Controls collection...
Also ... you state ...
>> All table fields are born equal, their variants and a variant can be null and will sometimes bite when least expected <<
All table fields EXCEPT booleans, do seem to be born equal (but not saved equal ) ... in the Edit Buffer (Copy Buffer, New Record Buffer ... whatever..) all field types (except boolean) seem to be of some of Variant persuassion at this stage simply because they CAN hold a Null (I actually view that as specialized variants simply because they can not take on any form, they can take on only one form...that of the data type) ... but .. the boolean is different ...
{Note: all Access generated default values were removed ... ie: the default 0 for Numeric fields}
The above produces this in the immediate window ...
Code:
Boolean: False:11:False
Integer: True:1:<Null>
Date : True:1:<Null>
Byte : True:1:<Null>
Text : True:1:<Null>
Memo : True:1:<Null>
So ... all but a boolean field was intantiated as a Null ...
.......
Along with all, this I wholeheartedly agree that, as Doc says ... you will see something null or empty somewhere that your logic didn't expect to see. The end of post #15 alludes to that as well.
If my arguments have not defended my claims (that a Yes/No FIELD can not be Null ... and your sample in db8 was using a CONTROL), please point out the shortcomings in my presentations. I highly respect your abilities in general, as well as your ability to ferret out issues and such as that.
MyBoolean in sample db8.zip in post #10 is not a control it’s a Boolean.
We can pass the Boolean and receive it as a Boolean until such time it goes Null.
If MyBoolean is a control we should be able to pass the control and receive it as a control, but we can’t.
If we make the receiving type a control it always fails with a type mismatch error.
Meaning that the type of argument being passed is not the same as the type of argument being received.
In the following, try changing ‘SomeBoolean As Boolean’ to ‘SomeBoolean As Control’
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
DoMyThing MyBoolean
End Sub
Sub DoMyThing(SomeBoolean As Boolean)
MsgBox "We got here."
End Sub
This is enough proof to me that MyBoolean is not a control because the compiler refuses to receive it as a control.
Also, if we delete all the controls on the Form, but leave MyBoolean in place, and do a MsgBox Me.Controls.Count we get 0.
Meaning that MyBoolean is not a control.
It’s in the Field List and not the controls collection, meaning it’s a Field and not a control.
This baby not only walks like a Field, talks like a Field, is in the Field list, is not in the control collection, the compiler handles it like a Field and refuses to handle it like a control, has no properties other than Value…it’s a Boolean Field.
Testing, testing oh testing…
VBA is difficult to test because of the way it can almost typecast a silk purse from a sow’s ear.
If we look at the following we can see a Function with no return datatype.
That datatype defaults to Variant and should be set to Null, which it is.
When it returns, the return datatype is presented to the MsgBox function for display.
The MsgBox function can’t accept a Null for display, so it should crash.
It doesn’t but instead displays a ZLS…why?
Code:
Private Sub cmdMyCommandButton_Click()
MsgBox MyFunction
End Sub
Function MyFunction()
End Function
If we were to assume that the MsgBox must crash on Null then we could be lead to the conclusion that Functions are not initialized to Null.
The same is also true of numerical data types (a Boolean is a numerical datatype).
When the above Function returns its datatype to a numerical datatype it will display as zero (0) False even though a numerical datatype can not contain Null.
So, under some circumstances, an uninitialized Variant can get converted to the datatype of the receiving variable, when the receiving variable datatype is known.
------------------------------
What’s been missing from this discourse so far is the intermediate step that can occur.
The intermediate step is called Empty.
A Variant starts out in life as Null. If the compiler needs to assign that Null to a non-Variant it can cast it to Empty. When the Empty is presented to the receiving variable the Empty will be converted to a ZLS for string datatypes and a zero for numerical datatypes, and it follows False for Boolean datatypes.
Because of all this typecasting going on, VBA is difficult to check. I particularly dislike the immediate window for testing as there is no control over the datatypes used. I suspect some of the results you got will have been due to ‘silk purse from a sow’s ears’ conversions.
------------------------------
But, to me, two facts still remain…
A Boolean Field can raise an error due to it being Null and that that Null needs to be handled.
Thank you for that analysis, and I definately see your points, and of course I have a reply , but I will try to be brief (HAH Me?, Brief!!, ) ...
>> If MyBoolean is a control we should be able to pass the control and receive it as a control, but we can’t.<<
We can't, because its a "special" hidden control that the Access UI creates in order to interface with field, kinda of like a bridge to the field ... for example:
Me.MyBoolean <-- this references the hidden control
Me.Recordset.Fields!MyBoolean <-- this references the field directly
My code in post #15 addresses this point as it is apparent that you did NOT create a Control named MyBoolean, yet "MyBoolean" is part to the controls collection. Please remove ALL controls from Form1 in db8.mdb, then put the following procedure in a standard module ...
Code:
Private Sub ItsAHiddenControl()
DoCmd.OpenForm "Form1"
With Forms("Form1")
MsgBox "MyBoolean " & .MyBoolean & ":" & .Controls("MyBoolean") & ":" & .Controls.Count
MsgBox "MyBoolean " & .MyBoolean & ":" & .Recordset.Fields("MyBoolean") & ":" & .Recordset.Fields.Count
End With
End Sub
When you run the code you will get a message box that says ...
MyBoolean:0:0
Then another one that says ...
MyBoolean:False:2
Note that, in the first message box, I reference MyBoolean through the Controls collection (but there is not a single control on the Form!!), and the Controls count remains 0. That supports the claim that it is a hidden (special) control that Access's UI uses to interact with the Field through the form.
The second message box interacts directly with the fields of the bound recordset, thus the coersion of the 0 into "False", and the indication of the number of fields in the field list.
..........
Ok ... if defineing SomeBoolean as a Control is enough to prove its a Field due to the error generated, then try defining SomeBoolean as a Field ... like the following:
Code:
Private Sub Form_Current()
DoMyThing MyBoolean
End Sub
Sub DoMyThing(SomeBoolean As Field)
MsgBox "We got here."
End Sub
You will get a Type mismatch error as well ... (note the Field is a DAO field since DAO is referenced first .. but the same error occurrs if you declare explicitly as a DAO or ADODB) .... so ... "MyBoolean" passed as we have it, and with the same justification, its not a Field either ... (my post #15 type named it as an "AccessField")
....
In your message box example ... it sounds convincing ... but there is a key fault in your logic ... a Variant starts out life as Empty ... not Null. The value of Empty is 0 if used in a numerical context, and a ZLS if used in a string context, thus there was no error in the MsgBox function call ...
From the help on the Variant data type (near the bottom):
The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context and a zero-length string ("") if it is used in a string context.
Private Sub MyMessageBox()
MsgBox "Is it Null: " & IsNull(MyFunction) & " Is it Empty: " & IsEmpty(MyFunction)
End Sub
Function MyFunction()
End Function
The result of the msgbox varifies that Variants start life as Empty. Which brings up something else ... I have always thought of Controls as being "Variants" .. but maybe not, since Controls start off as Null .... hmmm ... I guess in reality a Control, or Field for that matter, is not a "Variant" because a "Variant" is a VBA type, and a Control (or Field) is an Object type can can have a value of Null (except for a Boolean field ), but a Control/Field is not a Variant simply because a Control can not transform into just about anything like a Variant can.... They are two completely different Types!! ... I don't mean to diverge into something else ...
... I gotta run for now ... so I hate to leave this "unclosed" per se ... but I am being drawn to other responsibilities at the moment!!! ... you know the 7 kids thing! ...
IF Brent's post is correct, it does seems to provide a strong argument as to why controls should be renamed other than the default given by Access (especially if that control was created by either drag'n'drop or wizard), which will have same name as the field it is bound, and thus is ambiguous; are we referencing the form's control or the quasi-nether-voodoo-control or the recordsete's field, of which each may behave a bit differently.
I wonder if this has to do with the fact that I basically ended up doing this three-way test for a numeric field:
Code:
If Not IsNull(MyControl) And
Not MyControl = 0 And
Not MyControl = "" Then
MyControl being a named textbox, which I recall having problems where I certainly did not expect to have (e.g. numeric fields shouldn't support a ZLS, but it is a possibility with the control, and thus may not fire the events if I didn't test for that) and added more work when it could be something as simple as this:
Code:
If HasNoData(MyControl) Then
is really all I wanted. Now, that can be made a function, but it just begs the question- why didn't we have that functionality in first place? If there's going to be implicit typecasting going in background, the impetus is now upon the Access team to make sure that the typecasting works in all situations, which we very well know is quite impossible.
BTW, Chris, you seems to be a big fan of duck typing. I'm not quite sure if I would call this duck typing (though you didn't either, but alluded to it by saying that if it walks, talks and act like so and so, it's a so and so) because in a true duck typing, (and hopefully I'm not mistaken), we wouldn't be using variants or similar generic variants, rather assigning the most explicit data type for what we're doing with it, so in Python, setting a variable to a integer 2 would make it a integer, not a variant with typeof integer.
Do you know if we take the r out of Brent we might have a Bent Banana?
The saying…
“If it walks like a duck and quacks like a duck…it’s a duck.”
while not being in common everyday usage in Australia it is a saying that I think most people would understand on first hearing.
Common usage would mean…when we strip away the bark, surroundings, camouflage, hype, façade, baggage, pretense, garbage… things are more than likely to be what they appear to be.
So the saying has uses outside the realm of programming and simply may lead us to yet another subject within this thread.
So, it might be good subject matter for another thread, but I’m out of this one.
ROFL!! ... ... aren't banana's already bent? ... its a special feature that is hidden if you look at it a certain way! ...
>> Let’s stop expanding the problem and cut to the chase....How should we treat MyBoolean… <<
Well ... that diverges the discussion a bit, but I will add that the core of all my posts were intended to support the fact that a Yes/No field will not hold a Null.... but as you, and Doc, have stressed, its important to understand the behavior you have shown... so, with your db8.mdb as a basis for the following ...
If IsNull(Me.MyBoolean) Then ...
... Is completely valid and may be necessary to prevent errors because the MyBoolean control can return a Null.
If IsNull(Me.Recordset!MyBoolean) Then ...
... Is completely useless because the MyBoolean field will never be Null.
So, to me, the way we "handle" things depends on us knowing how what we are handling behaves.... but isn't discussing the behavior a divergence...
Oh ... I just re-read some of the thread ... and you said ...
>> I particularly dislike the immediate window for testing as there is no control over the datatypes used. I suspect some of the results you got will have been due to ‘silk purse from a sow’s ears’ conversions <<
I just want you to know that none of my testing was intiated from the immediate window .. I just used it for output for my code ...
Perhaps, but error checking around the statement IS still valid for another reason. In theory it is possible even for a Me.Recordset to have no current record after certain other me.recordset.{methods} - and in that case, IsNull might not trigger but a recordset error COULD trigger.
I'll state it another way. When designing a database, you have to do the impossible task of predicting the unpredictable. It makes you paranoid. But eventually you learn how to do better predicting of the semi-predictable. If your boss wants a working product as your delivery item then you are implicitly being PAID to be paranoid anyway. So you should assume that some level of error checking is needed even after innocuous statements relating to something that supposedly cannot be a null.
Stated yet another way, I have learned that Ed Murphy (he of "Murphy's Laws") was an OPTIMIST! Anything that CAN go wrong, WILL go wrong... right? Well, it is worse than that. Sometimes, even things that CAN'T go wrong do so anyway.
This doesn't mean that you must permeate your code with error traps and other crazy "what if the unimaginable happens?" testing. But it is possible to put some effort at key points to verify that some critical event HAS occurred correctly - and then make your assumptions thereafter.
In the case elaborated above, if you referenced that recordset with the boolean in it, that could go wrong if the recordset had gone away on you. So in a code sequence that depends on a recordset, test the recordset at the top of the sequence as a simple sanity check - and THEN start working on the fields, serene in the knowledge that they are actually there. And if they aren't there, your sanity check proves you WEREN'T insane to put that code where you did.
I program according to the philosophy of making key sanity checks before plowing full speed ahead. When you are about to dive in headfirst, don't only test the temperature of the water. Make sure the pool isn't empty. I.e. look before you leap.
