How to Convert Numbers to Text in Microsoft Access

by Hollie Miller
(UK)

Microsoft Access is a database management tool.

The data within a database must be stored in appropriate fields depending on the data type i.e. numerical values, alphabetic characters or Boolean values. There are restrictions on how certain data types can be modified and manipulated so sometimes it might be more useful to have numbers stored in a text field than a number field For example if a program you want to export the data to requires this format, or perhaps you need to make some changes to the data that aren’t permitted in a number field, such as adding non numerical characters.

This guide will cover the method to convert number fields to text fields in Microsoft Access by using an update query. To do this, the Cstr function is used. This converts a value to a string.

This is a 2 step process:

Note: As you will be dealing with an update query that will permanently change the results in your table, you may make to make a copy of your table(s) just in case you make a mistake.

Step 1 - Select the records to change

Open the database that contains the records you want to change.

Go to Design > Macros & Code > Query Design

This should open the query designer and the Show Table window

Go to the tab titled Tables

Select any tables that contain the records you want to change. Click Add then Close.

A window will appear showing all the fields in each table you selected. Double click on the fields you want to update, in this case the number fields you want to change to text.

The selected fields are shown in the Field row in the design grid.

Go to the Design tab and click on Run.

Check the results are the fields you want to change and remove any unwanted results by selecting them and pressing the delete key.
If you need to add any fields, you can drag additional fields onto the design grid.

Step 2 - Update the records.

Go to the tab titled Design under the group called Query Type click on Update

This has changed a select query to an update query. A new row titled Update To is now shown in the design grid.

Now, to change a numeric value to a text string, you can enter:

TextField:Cstr([NumberField]) in the Update To box.

Here, TextField represents a name you wish to give the text field, and NumberField is the name of the numeric field that you wish to convert to text. An example would be: a numeric field is called Quantity, in this case the command to convert the numbers representing a Quantity, to text would be:

QuantityText:Cstr([Quantity])

Go to the Design tab and in the Results group click on Run.

A warning message will appear, to run the query and update the results click Yes.

Update queries can be very useful in converting between data types and there are many different update functions that can be used in them, it’s not limited to just converting from numeric values to text. There are no restrictions on converting numbers to text, however there are some restrictions on some data type conversions and you should check this before using other update functions.

Bio - Hollie Miller
Hollie provides Microsoft Access Training for Acuity Training. In her spare time she loves to go to the gym and dress making.