For standard field input, is there any data type that would require use of a Variant?

[mdlueck]

For Access field value input of any standard data type... is there a datatype which would loose data/precision passing through a String variable as opposed to a Variant variable?

I realize use of the Variant type would be necessary to be able to store more specialized data type, but for sake of this question all of those datatypes are out of scope.

Consider the following datatype validation code:

Public Function datatypevalidation_IsByte(ByRef vntExpression As Variant) As Boolean 
  On Error GoTo Err_datatypevalidation_IsByte 
 
  Dim bytTest As Byte 
 
  bytTest = vntExpression 
  datatypevalidation_IsByte = (CStr(bytTest) = CStr(vntExpression)) 
 
Exit_datatypevalidation_IsByte: 
  Exit Function 
 
Err_datatypevalidation_IsByte: 
  'Bad Return Code 
  datatypevalidation_IsByte = False 
 
  Resume Exit_datatypevalidation_IsByte 
 
End Function

When I coded these validation functions, I specifically received in the data value to be inspected into a Variant variable.

My other use of a Variant is I took the original author's lead in building a collection class in VBA in that tFalse bln
False byt
True cur
True dtm
True dbl
True dec
False int
False lng
True sng
True str
True vnt

he variable to fetch out a certain object stored in the collection is a Variant so that it is safe for numeric of string collection object identifiers.

In both of these suggested cases, is there actually a valid reason to transport any of the standard VBA variable data types in a Variant rather than simply use a String to transport it? Boolean, is a String truly not capable of transporting a Boolean correctly? So on and on through the VBA core simple data types.

Does anyone have examples of when using a String would loose some precision which a Variant would preserve?


Oh, looks like per my Test() function to smoke test all my data type validator functions, that there was no suitable VBA variable for the Access Dec data type, so for that I had to use a Variant. So maybe that is one singled out example of where using a Variant for a simple data type would be required.

Public Sub datatypevalidation_Test()

  Dim blnX As Boolean
  Dim bytX As Byte
  Dim curX As Currency
  Dim dtmX As Date
  Dim dblX As Double
  Dim decX As Variant
  Dim intX As Integer
  Dim lngX As Long
  Dim sngX As Single
  Dim strX As String
  Dim vntX As Variant

  blnX = CBool("100.1")
  bytX = CByte("100.1")
  curX = CCur("100.1")
  dtmX = CDate(#2/7/2013#)
  dblX = CDbl("100.1")
  decX = CDec("79,228,162,514,264,337,593,543,950,335")
  intX = CInt("100.1")
  lngX = CLng("100.1")
  sngX = CSng("100.1")
  strX = CStr("100.1")
  vntX = CDec("100.1")

  Debug.Print datatypevalidation_IsBool(vntX) & " bln " & blnX
  Debug.Print datatypevalidation_IsByte(vntX) & " byt " & bytX
  Debug.Print datatypevalidation_IsCur(vntX) & " cur " & curX
  Debug.Print datatypevalidation_IsDate(dtmX) & " dtm " & dtmX
  Debug.Print datatypevalidation_IsDbl(vntX) & " dbl " & dblX
  Debug.Print datatypevalidation_IsDec(vntX) & " dec " & decX
  Debug.Print datatypevalidation_IsInt(vntX) & " int " & intX
  Debug.Print datatypevalidation_IsLng(vntX) & " lng " & lngX
  Debug.Print datatypevalidation_IsSng(vntX) & " sng " & sngX
  Debug.Print datatypevalidation_IsStr(vntX) & " str " & strX
  Debug.Print datatypevalidation_IsVnt(vntX) & " vnt " & vntX

End Sub

Immediate Window:
datatypevalidation_Test
False bln True
False byt 100
True cur 100.1
True dtm 2/7/2013
True dbl 100.1
True dec 79228162514264337593543950335
False int 100
False lng 100
True sng 100.1
True str 100.1
True vnt 100.1

I am thankful,

 

[theDBguy]

Hi. I could be way off, but I was just going to say to consider the size of each data type. For example, does a string data type truly have enough room/space/capacity to represent the whole of each of the other data types?

 

[mdlueck]

Greetings theDBguy,

does a string data type truly have enough room/space/capacity to represent the whole of each of the other data types?

Good way of looking at the question!

Data Type Bytes Used Range of Values

String (fixed Length) Length of string 1 to 65,400 characters

String (Variable Length) Length + 10 bytes 0 to 2 billion characters


Those specs are well beyond any of the other data types. So seems from a size standpoint it would work.

But what about the integrity of all of the "characters" in those values stored in the other simple variable types? Are there any issues that a String could not represent accurately "xyz" odd character that one of the other data types successfully stores, and using a Variant would also accurately store?

I am thankful,

 

[Pat Hartman]

The reason for using properly typed variables is to save the interpreter(or compiler) work during execution. You can use variants if you expect to have null values passed to the procedure. You could use correctly typed variables but use the optional key word. In that case you would need to check if the field is empty before you used it in any other expression.

 

[CJ_London]

Is there actually a valid reason to transport any of the standard VBA variable data types in a Variant rather than simply use a String to transport it?

I agree with Pat - you can pass nulls for variants, but not strings so you would need additional code to convert a null to a zls prior to calling your function/transporting

Does anyone have examples of when using a String would loose some precision which a Variant would preserve?

perhaps date/time datatypes might lose some precision at the fractions of second level

 

[mdlueck]

Thank you Pat and CJ,

Yes, I have no need to pass real null's in my code. vbaInet and I went round and round on that a few years ago. I coded a safe function to read field controls, and posted it here:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?t=221107&page=3#post1131115

My other use of Variant is for the Collection Class solution I use... but there again I can safely use a String to pass a Long Integer in.

I think I shall switch my Variant code to String code.

I appreciate all of the input into this thread. I will report back here if some unexpected turn of events happen due to my change to Strings.

I am thankful,

 

[The_Doc_Man]

It seems to me that one can always use a variant for input but there is a subtle conceptual issue to be considered. If you truly do not know what you expect, your validation code becomes incredibly difficult. If your business model requires you to "expect the unexpected" then you can use a variant - but I cannot recall a case where one was ever truly needed. And I don't know that you can bind a variant into a table so you HAVE to do something constructive with it anyway.

As has been mentioned, you can't bring in a null to any variable type except a variant. It IS possible to declare that a particular field in a table is NULL through some gyrations, but your point is that you were going to check on whether you got a null as input so you could do something about it. My concern is that if your data can be totally amorphous, what does it really mean?

I think that the combination of a text box with string input plus form-based validation methods would work just as well as a variant because, as you point out, there is no loss of precision in any of the numeric data types if you use string representations.

Of the several biggest possible numeric variants you have

A. DOUBLE - IEEE 754 (binary64 format) with up to 16 digits, up to a 3-digit exponent, decimal place, and two sign characters - total 22 characters.

B. QUAD - an integer capable of 19 digits. Add in a sign and you need 20 digits.

C. CURRENCY - a scaled QUAD with the same number of digits but you need a decimal point because of the scaling, so 21 digits.

D. Access doesn't use this data type, but IEEE 754 defines an quad-float (binary 128) that would require sign, exponent sign, 5 digits of exponent, a decimal place, and up to 36 digits of mantissa. You can do that in 44 digits.

E. Access DOES use a DECIMAL data type that gives you a sign, 28 digits, and (when scaled) a decimal point, total 30 characters. See end of this post for a note from FMS, a reputable information source, that suggests to never use DECIMAL data types because of issues in how they are handled.

F: Storing a date is the same as storing a DOUBLE except that the strings don't convert exactly due to limitations in the MS date conversion routines. You can give an exact date and time to the second in dd/mm/yyyy hh:nn:ss, or 19 characters. If you tried to add in fractions of a second, the date routines won't handle it. Spelling out the name changes the slashes to spaces and makes mm become 10 characters for long month names, so that pops it up to 27 characters. Adding in AM or PM adds a space and two characters for a total of 30.

Using just a string, you can store anything that someone could reasonably enter. You already ruled out any of the BLOB cases (binary large objects) such as OLE objects, attachments, and the like as being "out of scope" for the question.

So as an intellectual exercise I would say you COULD use variants - but once you had to store them, you would have to convert them anyway. So what's the point? You have yourself concluded that you might as well use strings, and I agree with you.

Note that there are known issues in the "native" DECIMAL data type.

https://www.fmsinc.com/MicrosoftAccess/database-design/decimal_data_type/index.htm

A VARIANT doesn't do DECIMAL very well because of the way it works. A VARIANT attempts to "map" a data value to another data type because (as I understand it), that variant is actually an internal data descriptor that applies to scalar variables. This is what a VARIANT descriptor looks like in C, and I doubt that it is much different internally for VBA.

https://docs.microsoft.com/en-us/windows/win32/api/oaidl/ns-oaidl-variant

In the article, you can see that while data types such as BYTE, WORD, LONG, SINGLE, and DOUBLE are multi-cast as themselves (SINGLE is actually "FLOAT"), the DECIMAL cast is a pointer as indicated by the leading asterisk in that complex UNION statement.

 

[Pat Hartman]

The thing I always hated most about having to interface with SAP when I was doing mainframe work was that every column was defined as a string. That left the burden of conversion on my code since to work with the data, i needed it to be a date or a Boolean or a long integer, etc. It also allowed users to enter invalid values since their error checking left something to be desired.

Programming languages have data types for a reason. Why do you think you know better?

 

[mdlueck]

Thank you very much The_Doc_Man! Exactly the sort of answer I was seeking. Saving that for reference!

Yes math / arithmetic is challenging in programming languages which do not implement decimal arithmetic support. Rexx / ooRexx natively support decimal arithmetic. I forget what a pain it is in other languages... frequently reminded all is not simple across language to language to language.

I am thankful,