Function to Get Maximum and Minimum Number of a Data Type

In some circumstances, we want to know what the minimum and maximum number of a data type in Access is. That is, the range of a data type. Knowing the range of a data type would inform us how to determine the field size, thus to save memory used and improve loading speed. The following table shows the data types and ranges as quoted from Access Help.

Data type	Range
Byte	0 to 255
Integer	-32,768 to 32,767
Long (long integer)	-2,147,483,648 to 2,147,483,647
Single (single-precision floating-point)	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	-9,999,999,999,999,999,999,999,999,999 to 9,999,999,999,999,999,999,999,999,999 (no decimal point)
Text	0 to 255 characters
Memo	0 to 65,536 characters

Using VBA code, we can create functions to determine the minimum and maximum value of a data type.

Public Enum enumMinMax
  numMin = 1
  numMax = 2
  numZero = 0
End Enum
Function rangeInt(Optional intEnumMinMax As enumMinMax) As Integer
  If intEnumMinMax = 0 Then rangeInt = 0
  If intEnumMinMax = numMin Then rangeInt = -32768
  If intEnumMinMax = numMax Then rangeInt = 32767
End Function
Function rangeLong(Optional intEnumMinMax As enumMinMax) As Long
  If intEnumMinMax = 0 Then rangeLong = 0
  If intEnumMinMax = numMin Then rangeLong = -2147483647 - 1
  If intEnumMinMax = numMax Then rangeLong = 2147483647
End Function
Function rangeByte(Optional intEnumMinMax As enumMinMax) As Byte
  If intEnumMinMax = 0 Then rangeByte = 0
  If intEnumMinMax = numMin Then rangeByte = 0
  If intEnumMinMax = numMax Then rangeByte = 255
End Function
Function rangeCurrency(Optional intEnumMinMax As enumMinMax) As Currency
  If intEnumMinMax = 0 Then rangeCurrency = 0
  If intEnumMinMax = numMin Then rangeCurrency = CCur("-922,337,203,685,477.5808")
  If intEnumMinMax = numMax Then rangeCurrency = CCur("922,337,203,685,477.5807")
End Function
Function rangeSingle(Optional intEnumMinMax As enumMinMax) As Single
  If intEnumMinMax = 0 Then rangeSingle = 0
  If intEnumMinMax = numMin Then rangeSingle = -3.4028235E+38
  If intEnumMinMax = numMax Then rangeSingle = 3.4028235E+38
End Function
Function rangeDouble(Optional intEnumMinMax As enumMinMax) As Double
  If intEnumMinMax = 0 Then rangeDouble = 0
  If intEnumMinMax = numMin Then rangeDouble = -1.79769313486231E+308
  If intEnumMinMax = numMax Then rangeDouble = 1.79769313486231E+308
End Function
Function rangeDecimal(Optional intEnumMinMax As enumMinMax, Optional byPrecision As Byte, Optional byScale As Byte) As Variant
  Dim maxDecimal, minDecimal, tempDecimal As Variant
  
  maxDecimal = CDec("9,999,999,999,999,999,999,999,999,999")
  minDecimal = CDec("-9,999,999,999,999,999,999,999,999,999")
  
  If byScale > byPrecision Or byPrecision > 28 Then
    rangeDecimal = "Error"
    Exit Function
  End If
  
  If intEnumMinMax = 0 Then rangeDecimal = 0
  
  If intEnumMinMax = numMin Then
    If byPrecision = 0 Then
      rangeDecimal = minDecimal
      Exit Function
    End If
    rangeDecimal = -(CDec(String(byPrecision, "9")) / 10 ^ byScale)
  End If
  
  If intEnumMinMax = numMax Then
    If byPrecision = 0 Then
      rangeDecimal = maxDecimal
      Exit Function
    End If
    rangeDecimal = CDec(String(byPrecision, "9")) / 10 ^ byScale
  End If
    
End Function
Function rangeText(Optional intEnumMinMax As enumMinMax) As Integer
  If intEnumMinMax = 0 Then rangeText = 0
  If intEnumMinMax = numMin Then rangeText = 0
  If intEnumMinMax = numMax Then rangeText = 255
End Function
Function rangeMemo(Optional intEnumMinMax As enumMinMax) As Long
  If intEnumMinMax = 0 Then rangeMemo = 0
  If intEnumMinMax = numMin Then rangeMemo = 0
  If intEnumMinMax = numMax Then rangeMemo = 65400
End Function

All the above functions have at least one parameter in common named intEnumMinMax. Using this parameter, we select one of the three available parameters: numMin (the most minimum/smallest number). numZero (zero number), and numMax (the most maximum/largest number).

Implementation

When we type one of the functions above, VBA displays IntelliSense as seen in the following figure:

The above figure show rangeInt function that displays enumeration declared from the statement Public Enum enumMinMax. Since the function parameter is optional, we may opt one of the available options or leave it blank.
The following examples run those functions in Immediate Window:

?rangeInt()
 0 
?rangeInt(numMax)
 32767 
?rangeInt(numMin)
-32768 
?rangeCurrency(numMax)
 922337203685477.5807 
?rangeDouble(numMin)
-1.79769313486231E+308 
?rangeByte(numZero)
 0 
?rangeDecimal(numMax,5,2)
 999.99 
?rangeDecimal(numMin,5,2)
-999.99 

The last two examples show the rangeDecimal function for decimal data type that explained below.

Case for Decimal Data Type

All functions have one parameter included, except for decimal data type, in which we create function name rangeDecimal that has three parameters. The first parameter is intEnumMinMax as explained above. The second and third parameters are related with the decimal behavior. The decimal data type uses precision and scale properties. Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. The default maximum precision of decimal data types is 28.

Suppose we have a field named unitPrice (Caption=Unit Price) in a table and set the precision of a decimal data type to 5 and its scale to 2 as seen in figure below:

In a datasheet view, when typing 1000 as the value for column named Unit Price seen in the following figure, an error message occurred, saying that the decimal field's precision is too small to accept the numeric you attempted to add.

Typing -1000 will display the same error message, as well. It is because we have set the decimal precision to 5 and its scale to 2. Thus the field may only accept the range value between -999.99 and 999.99. Outside this range, an error message is displayed.

The precision and scale in a decimal data type indicate the minimum and maximum number we can type in. For example, a decimal data type with precision is p and scale is s can be written as follows:

Decimal(p,s)

means that maximum number is 9 with p digit length and the number of digits to the right of the decimal point is s length. Using the above example, decimal(5,2) means the maximum number is 99999 (9 with 5 digits length) and 2 is the length of decimal point. Thus, we get 999.99

Another example, decimal (8,3) means the maximum number is 99999999 (9 with 8 digits length) and 3 is the lenth of decimal point. Thus, we get 99999.999. The same thing applies to the minimum number in which we can put minus sign as the negative number. Thus, the minimum number for decimal(5,2)  is -999.99 and the minimum number for decimal(8,3)  is -99999.999.