If you are creating a table using the Microsoft Access Object Library or DAO and you want a field that can handle regular decimal values without regards to precision, specify its data type as dbSingle or DB_SINGLE. Here is an example: Private Sub cmdCreateTable_Click()
Dim dbThisOne As DAO.Database
Dim tblEmployees As DAO.TableDef
Dim fldEmployeeNumber As DAO.Field
Dim fldFullName As DAO.Field
Dim fldWeeklyHours As DAO.Field
' Specify the database to use
Set dbThisOne = CurrentDb
' Create a new TableDef object.
Set tblEmployees = dbExercise.CreateTableDef("Employees")
Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
tblEmployees.Fields.Append fldEmployeeNumber
Set fldFullName = tblEmployees.CreateField("FullName", dbText)
tblEmployees.Fields.Append fldFullName
Set fldWeeklyHours = tblEmployees.CreateField("WeeklyHours", dbSingle)
tblEmployees.Fields.Append fldWeeklyHours
' Add the new table to the database.
dbThisOne.TableDefs.Append tblEmployees
dbThisOne.Close
End Sub
If you are creating the column using the SQL, specify its data type as SINGLE, FLOAT4, or IEEESINGLE.
One of the most popular categories of number used in most computer applications is the decimal number with double-precision. When creating a table in Microsoft Access, to create a field that can hold very large decimal and precise numbers, display the table in the Design View. After specifying the field name, set its data type to Number. In the lower section of the window, click Field Size and select Double. If you are programmatically creating a table using the Microsoft Access Object Library or DAO, to create a field that can hold decimal numbers with double-precision, specify its data type as dbDouble or DB_DOUBLE. Here is an example: Private Sub cmdTableCreator_Click()
Dim curDatabase As Object
Dim tblEmployees As Object
Dim colFullName As Object
Dim colWeeklyHours As Object
Set curDatabase = CurrentDb
Set tblEmployees = curDatabase.CreateTableDef("Employees")
Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
tblEmployees.Fields.Append colFullName
Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
tblEmployees.Fields.Append colWeeklyHours
curDatabase.TableDefs.Append tblEmployees
End Sub
If you are creating the table in SQL, specify the data type of the column as NUMBER, NUMERIC, DOUBLE, FLOAT8, or IEEEDOUBLE.
After creating a decimal field, you can use it as you see fit. For example, you and the user can perform data entry on the field. To specify the value of a number-based field, assign a natural or decimal number to it.
When creating a table in the Design View, if you want a column to hold monetary values, you can set its data type to a currency. If you are creating a column on a table, display it in Datasheet View then click a column header or a cell under it. In the Data Type & Formatting section of the Ribbon, click the arrow of the Data Type combo box and select Currency:
If you are creating the table in Design View, after specifying the column name, set its Data Type to Currency:
After setting this data type, when a person is using the application, the database would refer to the language set in the Control Panel and the rules in the Currency property page:
For example, if the database is being used in the US, the US dollar and its rules, including the $ symbol, would be applied. If you want to apply different rules to a particular column, after setting its Data Type to Currency, in the lower section of the window, click the arrow of the Field Size property and select the desired option:
If you are programmatically creating the column either in Microsoft Access Object Library or in DAO and you want it to hold monetary values, set its data type to dbCurrency or DB_CURRENCY. Here is an example: Private Sub cmdTableCreator_Click()
Dim curDatabase As Object
Dim tblEmployees As Object
Dim colFullName As Object
Dim colWeeklyHours As Object
Dim colHourlySalary As Object
Set curDatabase = CurrentDb
Set tblEmployees = curDatabase.CreateTableDef("Employees")
Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
tblEmployees.Fields.Append colFullName
Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
tblEmployees.Fields.Append colWeeklyHours
Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
tblEmployees.Fields.Append colHourlySalary
curDatabase.TableDefs.Append tblEmployees
End Sub
If you are using SQL, specify its data type as Money or Currency. Here is an example: Private Sub cmdTable_Click()
DoCmd.RunSQL "CREATE TABLE Employees(" & _
"FullName Text, " & _
"WeeklyHours Double, " & _
"HourlySalary Money);"
End Sub
Both Money and Currency have the same effect in Microsoft Access.
Data entry on a currency-based field follows the same rules as for natural numbers and decimal number fields. To perform data entry in a database, the user can display a table in Datasheet View or a form in Form View. For a currency field, the user can click it and type a natural or a decimal number. While the user can enter the currency symbol in a field, you cannot. If you are programmatically performing data entry on a currency-based field, assign only the value made of digits and possibly a decimal separator.
In our introduction to procedures, we saw different ways or creating procedures and functions. Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them.
One of the most valuable operations you will perform on a value consists of finding out whether it is numeric or not. To assist you with this, the Visual Basic language provides a function named IsNumeric. Its syntax is: Public Function IsNumeric(ByVal Expression As Variant) As Boolean This function takes as argument the value or expression to be evaluated. If the argument holds or can produce a valid integer or a decimal value, the function returns True. Here is an example: Private Sub cmdFunction_Click()
Dim Value As Variant
Value = 258.08 * 9920.3479
msgbox "Is Numeric? " & IsNumeric(Value)
End Sub
If the argument is holding any other value that cannot be identified as a number, the function produces False. Here is an example: Private Sub cmdFunction_Click()
Dim Value As Variant
Value = #12/4/1770#
MsgBox "Is Numeric? " & IsNumeric(Value)
End Sub
This would produce:
To assist you with specifying the color of anything, the Visual Basic language provides a function named RGB. Its syntax is: Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long This function takes three arguments and each must hold a value between 0 and 255. The first argument represents the ratio of red of the color. The second argument represents the green ratio of the color. The last argument represents the blue of the color. After the function has been called, it produces a number whose maximum value can be 255 * 255 * 255 = 16,581,375, which represents a color.
The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of –12 is 12. To let you find the absolute value of a number, the Visual Basic language provides a function named Abs. Its syntax is: Function Abs(number) As Number This function takes one argument. The argument must be a number or an expression convertible to a number:
Here is an example that retrieves the number in a text box named txtNumber, finds the absolute value of that number, and displays it in the same text box: Private Sub cmdAbsoluteValue_Click()
txtNumber = Abs(txtNumber)
End Sub
If you have a decimal number but are interested only in the integral part, to assist you with retrieving that part, the Visual Basic language provides the Int() and the Fix() functions. Their syntaxes are: Function Int(ByVal Number As { Number | Expression } ) As Integer
Function Fix(ByVal Number As { Number | Expression } ) As Integer
Each function must take one argument. The value of the argument must be number-based. This means it can be an integer or a floating-point number. If the value of the argument is integer-based, the function returns the (whole) number. Here is an example Private Sub cmdFunction_Click()
Dim Number As Integer
Number = 28635
MsgBox Int(Number)
End Sub
This would produce:
If the value of the argument is a decimal number, the function returns only the integral part. Here is an example Private Sub cmdFunction_Click()
Dim Number As Double
Number = 7942.225 * 202.46
MsgBox Int(Number)
End Sub
This would produce:
This function always returns the integral part only, even if you ask it to return a floating-point-based value. Here is an example: Private Sub cmdFunction_Click()
Dim Number As Single
Number = 286345.9924
MsgBox Int(Number)
End Sub
This would produce:
To assist you with evaluating an expression, the Visual Basic language provides the Eval() function. Its syntax is: Eval(Expression) The argument is passed to this function as a string. The argument can be as simple as an arithmetic operation as in 12 * 11, which would be Eval("12*11") or it can be a complex expression. When the function receives the argument, it uses its own built-in mechanism to analyze it and find out the type of the value it should return. If the expression appears as a calculation, then the function would return a numeric value. Otherwise, the function may return a string.
When an expression is supposed to produce a numeric value, it is important to make sure you get that value before involving it in another operation. Microsoft Office provides a function that can be used to easily get the numeric value of an expression. The function is called Val and its syntax is: Val(Expression) In reality, this function can be considered as two in one. In other words, it can produce either a natural or a real number. This function takes as argument either an unknown value or an expression, such as an algebraic calculation. In most cases, or whenever possible, you should be able to predict the type of expression passed as argument. For example, if you pass an algebraic operation that calculates the sum of two natural numbers, you should be able to predict that the function would return a natural number. In this case, you can retrieve the integer that the function returns. Here is an example: Private Sub cmdValue_Click()
Dim intValue%
intValue% = Val(145 + 608)
txtValue = intValue%
End Sub
In the same way, you can ask this function to perform an algebraic operation on two or more natural numbers, two or more decimal numbers, two or more numbers that include at least one decimal number. If the function receives an operation that involves two natural numbers, it would return a natural number. If the function receives an operation that involves at least one decimal number and one or more natural numbers, the function would return a decimal number. If the function receives an operation that involves decimal numbers, it would produce a decimal number. Regardless of the types of numbers that this function receives, you still can impose the type of value you want to retrieve. If the function receives an operation that involves only natural numbers, you may prefer to get a decimal number from it. If the function receives an operation that involves at least one decimal number and one or more natural numbers, you can still retrieve only the natural number. Here is an example: Private Sub cmdValue_Click()
Dim intValue%
intValue% = Val(455 + 1250.85 + 88)
txtValue = intValue%
End Sub
This call of the Val() function would return a decimal number but you mange to retrieve the natural number.
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Practical
Learning: Introducing Built-In Functions
.
The thousand numbers would display with a comma sign which makes it easier to read.
to remove one decimal value. You can continuously click the Decrease Decimal button 
to increase the number of digits
.
.
