Extract Text After a Dash in a Microsoft Access Query
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.
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.
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.
In the Access Navigation Pane, right-click the query and select Design View. Locate an empty column in the query design grid.
In the empty column's Field row, enter: SecondPart: Trim(Mid(Nz([CITY_NAME], ""), InStr(Nz([CITY_NAME], ""), "-") + 1))
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.
Return an empty string when the field contains no dash
Use an explicit condition if records without a dash should produce a blank result rather than the original text.
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.

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.




