Javatpoint Logo
Javatpoint Logo

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.

Val Function

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:

  • If you want the Val Function to convert the string into a numeric value, then the user needs to ensure that the string has the numeric value in the beginning; else, the function will simply return 0.
  • As discussed above, the Val Functions stops reading the string as it encounters the first non-numeric value and cannot be converted into a number on changing its data type. But there can be spaces between the numeric value. That is, the non-numeric character does not include the spaces in the string and will read the string further.
  • If there is no numeric value at the beginning of the string, then the function returns 0.

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.

Output: 10

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.

Output: 1015

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.

Output: 10

The above function returns 10. As since ':' is a non-numeric character, it will stop right there.

Output: 75

The value returned by the function will be 75. The numeric equivalent of 075 is 75 only.

Output: 0

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

Convert Text into Number in Ms Access

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.

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA