Fix a Data Type Mismatch in an Access Currency Query
Question details
Resolve a data type mismatch when formatting an Access query expression that uses Nz with two Currency fields.

- 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.
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.
Check the field references, joins, and underlying query
Use this approach to isolate the mismatch before changing the display format.
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.
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.
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.
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.
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.

Keep the query result numeric instead of using Format
Use a Currency display property when the result must also be calculated, joined, or compared as a number.
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.

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.




