Others

Fix a Data Type Mismatch in an Access Currency Query

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Resolve a data type mismatch when formatting an Access query expression that uses Nz with two Currency fields.

Fix a Data Type Mismatch in an Access Currency Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A query combines values from the Project_Details and Budget Copyright fields and applies Format with the Currency format.
Observed behavior
Access reports a data type mismatch even though both source fields are defined as Currency.
Before you start

Open the query in Design View and note the exact expression, joins, and criteria. In each source table's Design View, confirm the data type and spelling of the referenced fields.

Solution 1Recommended

Check the field references, joins, and underlying query

Use this approach to isolate the mismatch before changing the display format.

1
Verify the source fields

Open Project_Details and Budget in Design View. Confirm that each Copyright field is Currency and check that the query references the intended field rather than a similarly named field such as Design.

2
Test the fields separately

Open the query in Design View and add [Project_Details].[Copyright] and [Budget].[Copyright] as separate output columns. Run the query to see whether the error occurs before Nz or Format is applied.

3
Inspect joins and criteria

In Design View, inspect the lines between source tables and the Criteria row. Make sure joined fields and compared values have compatible data types, and check any source queries for expressions that return text.

4
Test Nz without Format

In a new query column, test the Nz expression without Format. If the goal is to add the two amounts, use TotalAmount: Nz([Project_Details].[Copyright],0) + Nz([Budget].[Copyright],0), then run the query.

5
Apply the display format

If the numeric expression works, select its column in Design View, open the Property Sheet, and set Format to Currency. Check any criteria or joins that refer to a formatted expression, because Format returns text.

Check the field references, joins, and underlying query
Choose the intended calculation: The addition example treats each Null as zero. If you need the first non-Null value instead of a sum, use an Nz fallback expression and test it separately.
Free Microsoft Office alternative

Work with exported Access data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with office files. Export Access query results to an Excel workbook to review them in WPS Spreadsheets; WPS does not repair Access queries or replace an Access database.

Review and format exported query results as currency in a spreadsheetOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why can Format cause a data type mismatch in Access?

Format returns text, even when its input is numeric. Check the query's joins and Criteria row for comparisons between the formatted result and numeric fields or values.

How do I display a calculated value as currency without converting it to text?

In query Design View, select the numeric calculated column, open its Property Sheet, and set Format to Currency. Keep the calculation itself numeric.

How do I handle Null values when adding the two Currency fields?

In a query column, use Nz([Project_Details].[Copyright],0) + Nz([Budget].[Copyright],0). This treats a Null in either field as zero before adding.

What if I need the Budget value only when the Project_Details value is Null?

Test Nz([Project_Details].[Copyright],[Budget].[Copyright]) in a query column without Format first. This selects the Budget value when the Project_Details value is Null; it does not add the two values.

Can WPS Office fix the Access query directly?

No. Correct the query in Microsoft Access. You can then export its results to an Excel workbook and open that workbook in WPS Spreadsheets.