Others

Extract Text After a Dash in a Microsoft Access Query

Muhammad TalhaMuhammad Talha Nov 13, 2026 869 views

Question details

Extract the text after a dash from the CITY_NAME field in an Access query while handling null values.

Product
Microsoft Access
Device & OS
not provided
Scenario
A field contains values such as “City Name - New York,” and the query should return only “New York.” Some records contain null values.
Observed behavior
The desired result is the trimmed text after the dash. A Data Type Mismatch in Criteria Expression error is also reported.
Before you start

Open the query in Design View and confirm that CITY_NAME is a text field in its source table or query. Check whether the values contain a standard hyphen (-), multiple dashes, or no dash.

Solution 1Recommended

Add a null-safe calculated field to extract text after the dash

Use this expression to return the text after the first dash and remove leading or trailing spaces.

InStr locates the first dash, Mid returns the text starting immediately after it, and Trim removes surrounding spaces. Nz([CITY_NAME], "") explicitly substitutes an empty string for null values.

1
Open the query in Design View

In the Access Navigation Pane, right-click the query and select Design View. Locate an empty column in the query design grid.

2
Enter the calculated field

In the empty column's Field row, enter: SecondPart: Trim(Mid(Nz([CITY_NAME], ""), InStr(Nz([CITY_NAME], ""), "-") + 1))

3
Run and check the results

Select Run on the Query Design tab. A value of “City Name - New York” should return “New York,” while a null value should return an empty string.

Values without a dash remain unchanged: If no dash exists, InStr returns 0, so Mid starts at character 1. This expression therefore returns the entire value with surrounding spaces removed.
Free Microsoft Office alternative

Work with Exported City Data in WPS Spreadsheets

WPS Office is a free, lightweight alternative for spreadsheet-based work with exported Access data. Its familiar interface and Excel format compatibility make migration straightforward, but it does not run Access queries or replace Access database functionality.

Open exported city data and use spreadsheet text functions to separate labels from city names.Supports Microsoft Excel .xlsx and .xls formats for spreadsheet workflows.Provides a familiar spreadsheet interface for reviewing and editing exported records.Offers a lightweight, easy-to-use option for working with CSV exports without modifying the Access database.
microsoft office alternative - wps office

Frequently Asked Questions

Why use Nz when extracting text in an Access query?

Nz([CITY_NAME], "") replaces a null field value with an empty string before the text functions process it. Enter it inside the calculated expression in the query's Field row; specifying "" makes the replacement value explicit.

What should I check if Access reports a Data Type Mismatch in Criteria Expression?

Open the source table in Design View and verify that CITY_NAME is a text field. Then open the query in Design View and inspect its Criteria rows and joins for incompatible types, such as text being compared with numbers or dates. Test the calculated field in a simple select query without criteria to help isolate the cause; Nz handles nulls but does not fix unrelated type mismatches.

What happens if a value contains more than one dash?

InStr finds the first dash, so the expression returns everything after that dash. For example, “City Name - New York - Downtown” returns “New York - Downtown.” Extracting text after the last dash requires a different expression using InStrRev.

Why does the query return the whole value instead of just the city name?

The basic expression returns the whole trimmed value when InStr cannot find a standard hyphen (-). Check the source value for a missing dash or a different character, such as an en dash (–). Use the conditional solution to return an empty string when no matching dash exists.