Fix Microsoft Access Query Concatenating Numbers Instead of Adding
Question details
The user is experiencing an issue where a Microsoft Access query joins numeric values together like text (concatenation) instead of mathematically adding them.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Performing arithmetic addition calculations within an Access query using functions like DCount or directly adding specific fields together.
- Observed behavior
- The query treats the numeric values as text strings, resulting in concatenation (e.g., 1 + 2 = 12) rather than the expected mathematical sum (1 + 2 = 3).
Ensure you have Design View access to the affected Microsoft Access query and the underlying tables to modify field data types or query expressions safely.
Use the Val() Function to Convert Text to Numbers
Wrap your field names or functions in the Val() function to force Access to treat the string values as numbers during the calculation.
When functions like DCount return a string data type, or if numeric values are mistakenly stored as text, Access uses the '+' operator for string concatenation instead of arithmetic addition. The Val() function extracts and converts text representations of numbers into actual numeric values.
Right-click your problematic query in the Navigation Pane and select 'Design View'.
Find the column in the query design grid containing your addition formula (e.g., FAssignments + BAssignments).
Modify the expression to wrap each field or function in Val(). For example, change it to: Val([FAssignments]) + Val([BAssignments]).
Click the 'Run' button (exclamation mark icon) on the Query Design ribbon to verify that the query now calculates the mathematical sum properly.

Change the Underlying Field Data Type to Number
For a permanent fix, correct the database schema by changing text fields that contain numeric data to the Number data type.
Need a simpler way to manage and calculate data? Try WPS Spreadsheet
While Microsoft Access is powerful for relational databases, many users find managing and calculating large sets of data much easier in a spreadsheet. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, featuring a powerful Spreadsheet program that handles data calculations seamlessly without complex data type errors.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installer.
- 2. Install the Suite: Run the setup file and follow the quick installation prompts to get started.
- 3. Open Your Data: Launch WPS Spreadsheet and easily import your exported Access data (CSV or Excel) for hassle-free calculation.

Frequently Asked Questions
Why does the DCount function return a string instead of a number in Access?
The DCount function is designed to return a Variant data type. Depending on the expression context in Access, it may be implicitly treated as a text string. Wrapping it in a conversion function like Val() or CDbl() forces Access to treat the result explicitly as a numeric value for arithmetic operations.
What is the difference between concatenation and addition in Access queries?
Concatenation (joining) occurs when Access uses the '+' or '&' operator on text data types, combining the strings side-by-side (e.g., '1' and '2' becomes '12'). Addition is the mathematical operation performed on Number or Currency data types, resulting in a mathematical sum (e.g., 1 + 2 = 3).
Can I use the CDbl() or CInt() function instead of Val()?
Yes. CDbl() converts values to a Double data type, and CInt() converts to an Integer. These functions are often preferred over Val() if your text data contains currency symbols or commas, which the Val() function might not parse correctly.




