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.
Using VBA code, we can create functions to determine the minimum and maximum value of a data type.
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:
The last two examples show the rangeDecimal function for decimal data type that explained below.
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.
| 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 |
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 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.
Comments
Post a Comment