Convert Text into Number in Ms Access
Converting Text Into Number in MS Access
In this module, we will learn to convert the numerical data stored in the field in MS Access into text. We will use the Val function to perform this function. We will learn the syntax and perform some implementations.
This function is used in MS Access to convert the string into numeric. The function takes a single value as an input and returns a numeric value as the output.
The parameter in Val Function
It takes a string value as an input parameter. The numeric value is derived from the string entered as the parameter.
Return Value in Val Function
The Val Function will return a numeric value derived from the string. It is the numeric part that is stored as a string data type.
Note: The Val function does not read the textual information, but it converts the data type of the numeric value stored as the string. The function reads the string from left to right which is entered as the parameter. It will stop reading the string when the function encounters the very first character that cannot be recognised as a numeric value in the string. This means that if you want to convert the data into a numeric value, the numeric part should be located at the beginning of the entered string. If there is any character before the numeric part, then it won't be able to convert that part into a number.
The syntax for Val Function
The syntax for implementing the Val Function is as follows:
Remember the following Points:
Versions of MS Access Supporting Val Function
The Val function can only be implemented in the following versions of Microsoft Access:
Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000.
If any updated versions in the MS Access are released after the mentioned version, then the user can refer to the release documentation to check if there is any change in the implementation of the Val Function.
Implementation of the Val Function in MS Access:
Let us discuss some examples to see the implementation in Microsoft Access.
The above function will return 10 as a result. The next character it will encounter after space will be m which cannot be converted into a numeric value.
The above function will return 1015 as a result. It will first encounter 10, then after several spaces, it will encounter 15, which will also be converted, but after that, the next character after space is i, which cannot be converted. Thereby, it will stop reading the string further.
The above function returns 10. As since ':' is a non-numeric character, it will stop right there.
The value returned by the function will be 75. The numeric equivalent of 075 is 75 only.
It will return 0 as the first character is T in the string, which is a non-numeric character.
Implementation of VBA Code
The Val Function can be implemented in VBA code in MS Access
After the conversion using the Val function, the LNo would store the value of 96.9. It stores the numeric value in the variable.
Implementation of Val Function in SQL Queries
To implement the Val function, use the following query:
The above query will return the numeric value located at the beginning of the Description field. It works as it works with a string value. The result after computation will be stored in Expr01. The user can use a column name instead of Expr01, making it easier to understand.