Others

Fix Microsoft Access Adding Text Fields Instead of Numeric Scores

Huda QurayshiHuda Qurayshi Nov 20, 2026 868 views

Question details

The user needs to add score fields numerically in a Microsoft Access expression instead of joining their values as text.

Fix Microsoft Access Adding Text Fields Instead of Numeric Scores
Product
Microsoft Access
Device & OS
not provided
Scenario
A table stores subject scores in separate fields, and the user wants to calculate a total for each student.
Observed behavior
The expression places field values side by side instead of adding them, which can occur when the fields contain text values.
Before you start

Open the table in Design View and check the data type of each score field. Review the existing values for nonnumeric text before changing a field type, because incompatible values may be lost during conversion.

Solution 1Recommended

Change score fields to Number and add them in a query

Use this approach when each subject is already stored in a separate table field and you need a total for those fields.

1
Inspect the field types

In the Navigation Pane, right-click the score table and select Design View. Check whether each score field is set to Short Text or Number.

2
Correct incompatible values

Open the table in Datasheet View and review score entries for nonnumeric text. Correct those entries before converting the fields; keep a copy of the original table if you need to preserve the existing values.

3
Convert the score fields

Return to Design View, select each score field, and change its Data Type to Number. Choose a suitable Field Size, such as Long Integer for whole scores or Double for decimal scores, and save the table.

4
Build the total expression

Select Create > Query Design, add the score table, and enter an expression such as TotalScore: Nz([Math],0) + Nz([Science],0) in a blank Field cell. Replace the sample names with your actual score field names.

5
Run the query

Select Run on the Query Design ribbon and check that the TotalScore column shows numeric sums.

Change score fields to Number and add them in a query
Handling missing scores: Nz([Math],0) treats a null score as zero in the expression. Omit Nz if a missing score should leave the total null instead.
Free Microsoft Office alternative

Calculate exported student scores in WPS Spreadsheets

For spreadsheet-based score calculations, WPS Office is a free, lightweight alternative with a familiar interface and Excel format support. Export Access data to a supported spreadsheet or CSV file to move it into WPS; WPS does not fix Access database fields or replace Access relationships and queries.

Add and summarize student scores with spreadsheet formulasOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for exported dataWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access join my scores instead of adding them?

The score fields may be stored as text. Right-click the table in the Navigation Pane, select Design View, and check each score field's Data Type.

How do I add two subject fields in an Access query?

In Create > Query Design, add the table and enter an expression such as TotalScore: Nz([Math],0) + Nz([Science],0) in a blank Field cell. Use your actual field names and ensure the fields are numeric.

What happens if a score is blank?

A null value can make an addition expression return null. Use Nz([FieldName],0) in the query expression if a missing score should count as zero.

How can I total scores stored in separate rows?

In a query's SQL View, use SELECT StudentID, Sum(Score) AS TotalScore FROM StudentScores GROUP BY StudentID; Replace the table and field names if yours differ.

Can WPS Office repair my Access database field types?

No. Change the field types in Microsoft Access. You can export data from Access to a spreadsheet or CSV file if you want to calculate scores in WPS Spreadsheets.