How to Convert String to Numbers in Access
Microsoft Access uses the expression tool to format and convert data types in a table. Access expressions are similar to formula fields in Microsoft Excel and can be used to evaluate fields in a table or query using a formula or function. Expressions include a combination of built-in and user-defined functions and constants. Access uses the "Val" function to convert text fields or any string expression to a number format.
Other People Are Reading
Instructions
-
-
1
Open your Access 2010 database and determine which text field or string you need to convert to a number format. If you are using an older or newer version than Access 2010, the conversion process is similar, but the location of the tools may vary.
-
2
Create a new query using the query wizard or open a saved query.
-
-
3
Select "Design View" from the "View" menu in the query.
-
4
Select the "Builder" option under the "Design" tab. The "Expression Builder" pop-up window will appear.
-
5
Choose the "Functions/Built-In Functions" option from the list on the left hand side of the "Expression Builder" window.
-
6
Select "Conversion" from the list in the middle and "Val" from the list on the right. Double click on the "Val" option to add the formula to the expression window at the top
-
7
Highlight the "<<stringexpr>>" area of the expression in the top window.
-
8
Choose the table, query or report from the left hand column of the "Expression Builder" that includes the field to be converted.
-
9
Double click on the text field you wish to convert to a number to add it to the expression.
-
10
Click "OK."
-
11
Select "Run" from the header menu. The displayed expression field will be in a number format.
-
12
Select "View Datasheet" from the "View" menu to see your results.
-
1
Tips & Warnings
The Val function will stop reading the data from the text string at the first non-number character that it encounters. For example the text "123#$56" will be converted to "123" and the remaining "#$56" will be dropped. Symbols and characters that are often paired with numeric values, such as dollar signs, decimals and commas will not be recognized by the Val function.
Expressions may be used in Access to format data in tables, queries, reports and forms.
References
- Photo Credit Thinkstock/Comstock/Getty Images
