Others

Fix Microsoft Access Query Concatenating Numbers Instead of Adding

Adam DavisAdam Davis Oct 15, 2026 869 views

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.

Fix Microsoft Access Query Concatenating Numbers Instead of Adding
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).
Before you start

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.

Solution 1Recommended

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.

1
Open Query Design View

Right-click your problematic query in the Navigation Pane and select 'Design View'.

2
Locate the Expression

Find the column in the query design grid containing your addition formula (e.g., FAssignments + BAssignments).

3
Apply the Val() Function

Modify the expression to wrap each field or function in Val(). For example, change it to: Val([FAssignments]) + Val([BAssignments]).

4
Run the Query

Click the 'Run' button (exclamation mark icon) on the Query Design ribbon to verify that the query now calculates the mathematical sum properly.

Use the Val() Function to Convert Text to Numbers
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS Office website and download the free installer.
  2. 2. Install the Suite: Run the setup file and follow the quick installation prompts to get started.
  3. 3. Open Your Data: Launch WPS Spreadsheet and easily import your exported Access data (CSV or Excel) for hassle-free calculation.
Seamless compatibility with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive UI that requires no database query knowledge for complex data calculations.Free, lightweight, and easy to install on Windows, Mac, Linux, and mobile devices.Built-in functions that automatically detect and format numeric data types to prevent concatenation errors.
QA img-9

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.