Odd Behaviour / Bug with Combo Column Values

[Minty]

I'll try and describe this as simply as possible.

Take a multi-column combo box where one of the columns is a Decimal/Number field sourced directly from a table.
The data type is a number type - Currency / Integer makes no odds, but it does allow nulls.

In VBA you assign that value to a variable for use later in a simple piece of code:

Private Sub cmdAddRecord_Click()
    
    ''' Just an example
    Dim ccDayRate As Currency
    Debug.Print "Value is: " & Me.cboRecords.Column(2)
    
    ccDayRate = Nz(Me.cboRecords.Column(2),0)
 
End Sub

All of a sudden having been doing this for years, your column value is now returning an empty string "" instead of a Null, and gives you a type mismatch error.
Nz() doesn't help because of the ZLS being returned.

When did this helpful feature get introduced?
Is it just me or have I just never encountered this weird behaviour?

Using : Microsoft® Access® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit
Simple example attached for clarity - pick the sales item in the drop down.

 

[Gasman]

Not sure I am doing this right?
I added another Debug for ccdayRate and amended values for different values

Value is: 0
ccDateRate is 0
Value is: 48
ccDateRate is 48

Dim ccDayRate As Currency
    
    Debug.Print "Value is: " & Me.cboRecords.Column(2)
    
    ccDayRate = Nz(Me.cboRecords.Column(2), 0)
    
    Debug.Print "ccDateRate is " & ccDayRate

Office 2019

 

[Minty]

Did you pick the Sales item from the drop down then press the button?

It works fine when there is any value in the table.

 

[Gasman]

No, and I just got your error.
I then ensured that value was Null by setting it in an update query, and still got your error?

 

[MajP]

I think in that case it is actually correct behavior. The listbox column returns strings and then they get cast . Are you sure you previously had an empty column 2 to test? A simple solution is
If (Me.cboRecords.Column(2) & "" <> "") Then ccDayRate = Me.cboRecords.Column(2)

Since dim ccDayRate as currency will default to zero when dimensioned.

 

[MajP]

Here is proof that is how a listbox works (at least in some cases)
Debug.Print VarType(Me.cboRecords.Column(2))
this returns 8 which is vbString

 

[Minty]

@MajP I would agree with you but surely I would get the type mismatch error when it returned a number?
Apparently not - Access is happy to automatically convert "48" to 48

I am utterly convinced I have used this type of construct dozens of time before and not had this error raised until recently.
Maybe as you say I've not had much missing data, or I've always returned string data, but I find that hard to believe.

Having tested it, it always returns a string as you stated, no matter what the underlying data type.
I feel that this is one of those behaviours that must have changed or by some fluke I have just never encountered before.
Live and learn.

 

[MajP]

@MajP I would agree with you but surely I would get the type mismatch error when it returned a number?
Apparently not - Access is happy to automatically convert "48" to 48

Yes I found often " why does this even work" is because VBA will automatically cast things to the expected datatype. This gets frustration because sometimes it is overly helpful and makes it confusing, while other times you expect it to cast and it does not.
Here are some observations

Checkboxes passed ByVal instead of ByRef

I have a set of routines that do various things to controls - enable, visible, lock and so on. It is faster, cleaner and prevents screen flickering when setting a property that is already set. Here is one such routine: Public Sub gbl_CtlsAbled(ByRef arr As Variant, ByVal Stav As Boolean) Dim i&...

www.access-programmers.co.uk

 

[The_Doc_Man]

Apparently not - Access is happy to automatically convert "48" to 48

Yes... well, no. Not specifically Access, ... but VBA is happy to do that. From the VBA Language Specifications, v20140424, section 5.5.1.2.4, Let-coercion to and from String

I would copy/paste the full text here but it is a huge section that gets deep into Backus-Naur notation on acceptable string content. Here is a partial quote:

Otherwise, if the destination type is a fixed-point or floating-point numeric type, and the signed value is within the magnitude range of the destination type, the result is the signed value converted to the nearest value that has a representation in the destination type.

Suffice it to say that "48" will, in the middle of whatever VBA is trying to do there, get quietly converted to INTEGER or LONG without even so much as a peep as long as there is no alphabetic character or punctuation inconsistent with the data type. In fact, there are even rules for date conversion and floating-point conversion as well. The discussion says (in essence) that if the string doesn't contain characters that would make it illegal as a number, it can be converted ("Let-coerced") to a number even though presented as a string. For ANY numeric format!