Javatpoint Logo
Javatpoint Logo

How to Convert the Numbers To Text in MS Access

MS Access enables the user to manage the databases. It makes it easier to perform operations on the database structure and data. The query functions are important functions that enable the user to perform computations in the database.

The data stored in the database must be stored inside the appropriate field. Not only must the field contain the relevant information, but it should also have the appropriate data type. This means that if the field is defined to store numerical values, alphabetical characters, or boolean, all the values under the field should be of the respective type only. For example, if a field contains numerical values such as 50, then the user cannot store the value as fifty in the field as it will be a string.

In MS Access, there are certain restrictions on the different data types and the number of times the user can either modify or manipulate the data types. Sometimes, it is better to convert the numerical values into the text field as transferring the results stored in string data types into other programs is comparatively easier.

The user can also perform conversion of the data types when working with multiple programs and the other programs require the data in a different format than the previous one. Moreover, there are certain restrictions to the numerical values that can be overcome if the user uses a string data type to store the value.

For example, the user cannot concatenate a numerical value with a non-numerical character. This feature is especially useful when the user wants to create ProductID or any other string that requires numerical value and non-numerical characters.

Suppose the ProductID constitutes the first three letters of the manufacturing country and the order date. If the manufacturing country is INDIA, the order date (DDMMYYYY) is 29072022. Then, the ProductID will be IND29072022. But to create the ProductID, the user must first convert the order date into a string value.

In this module, you will learn to convert the numerical data type into string data type by changing the types of fields in MS Access. The user is required to use the update query to perform the conversion. In MS Access, a predefined function allows users to easily convert the data of other data types into a string.

The conversion of a number into text is a 2 step process.

Note: It is advised to first produce a copy of the table in which you want to perform this operation. Because you will perform an Update operation to convert the numeric data into text, the changes performed using the update query are permanent. Thus, the data cannot be restored. So, the whole data can be lost if any error occurs during the conversion.

Step 1: The first step is to choose the records whose data types the user wants to convert into strings.

Follow the given steps to select the records from your database.

  1. Open the database with the records you want to change the data type into a string.
  2. Go to the design tab and select Macros and Code. In the macros and code, click on Query Design.
  3. It will open the query designer and display the Table Window.
  4. Click on the tab tiled Tables in the Show table window.
  5. From the list of all the tables, select the tables containing the records you want to operate. Add the records and then close them.
  6. A window will appear showing all the fields you selected. Double-click on the fields you want to update. In this case, the number fields you want to change to text.
  7. Once you have chosen all the fields for which you want to modify the value, the selected fields will appear in the Field row of the design grid.
  8. Now, after selecting the fields, go to the design tab and click on the Run option.
  9. Once you have clicked on Run, it will return the results. Now check that all the fields you wanted to modify are changed. In case of unwanted modifications, you can delete such results by selecting a field and pressing the delete key.
  10. Adding additional fields is also simple. Just click and drag the fields you want to modify the data onto the design grid.

This sums up the first step. Now the second step is as follows:

Step 2: Update the records

  1. To perform an update on records, first, go to the tab tiled design. The tab tiled design is located under the group named Query Type. Then click on Update.
  2. By clicking on Update, the select query will be modified to an update query.
  3. A new row will appear in the design grid. The title of the new row will be Update To.
  4. After performing the above steps, you can finally execute the commands for converting the numbers into text. The commands are to be typed in the Update To box.

In the above commands, TextField represents the field name in which you want to store the results after performing the conversion.

The NumberField contains the field's name, whose numeric values are to be converted into text.

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA