VBA & Macro Problems

Fix Access VBA InStr Cannot Find a Hyphen in Imported Text

Huda Qurayshi Sep 30, 2026 869 views

Question details

The user needs to locate a hyphen within imported Long Text using the InStr function in Microsoft Access VBA, but the search continually fails.

How to Fix Access VBA InStr Cannot Find a Hyphen in Imported Text

Product: Microsoft Access
Device & OS: not provided
Scenario: Running a VBA script to search for a specific hyphen character inside an imported Long Text field.
Observed behavior: The InStr function returns 0 when searching the imported text for a hyphen, even though the character visually looks like a standard hyphen and the search works correctly with string literals.

Before you start

Ensure you have access to the VBA editor in Microsoft Access and isolate a specific record where the hyphen search is failing for testing purposes.

Solution 1Recommended

Inspect and Replace Unicode Hyphen-Like Characters

Use the AscW function to identify the exact Unicode value of the imported hyphen and replace it with a standard ASCII hyphen-minus.

Imported text often contains special typographical characters, such as en-dashes or em-dashes, instead of the standard ASCII hyphen-minus (character code 45). Because the VBA InStr function requires an exact character match, treating these Unicode dashes as different symbols causes the search to fail.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Inspect the Character with AscW

Write a debug script using AscW(Mid(yourText, position, 1)) to output the exact numeric code of the apparent hyphen in your imported text.

Identify the Unicode Value

Check the Immediate Window (CTRL + G). If the returned code is not 45 (the standard ASCII hyphen), note the specific Unicode value (e.g., 8211 for an en-dash).

Normalize the Text

Use the Replace function in your script to convert the Unicode dash into a standard hyphen before searching: yourText = Replace(yourText, ChrW(8211), ChrW(45)).

Run the InStr Function

Execute your InStr search on the newly normalized text string. The function should now successfully return the correct position of the hyphen.

Inspect and Replace Unicode Hyphen-Like Characters

Text Normalization Complete: Once the Unicode characters are replaced with ChrW(45), standard VBA string functions like InStr will behave exactly as expected.

Free Microsoft Office alternative

Looking for a Better Office Suite? Try WPS Office

While Microsoft Access is built for complex database management, everyday data processing, text analysis, and macro scripting can often be handled much faster in a powerful spreadsheet. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, featuring robust VBA support for your daily automated tasks.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and macro-enabled workbooks (.xlsm).Lightweight architecture ensures quick installation and smooth performance on all devices.Familiar user interface allows you to transition your workflow seamlessly.Built-in advanced VBA and macro support for automating repetitive data tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Asc function return the wrong character code in VBA?

The standard Asc function in VBA only supports ASCII and returns the system's default ANSI character code. When analyzing text containing Unicode characters, such as special dashes, you must use the AscW function to retrieve the accurate Unicode code point.

How do I check if my imported database text contains hidden Unicode characters?

You can create a simple VBA loop using the Mid and AscW functions to print out the code of each character in your text string. Any returned value above 255 typically indicates a Unicode character that standard ASCII string functions might misinterpret.

Does the Long Text data type in Access cause the InStr function to fail?

The Long Text data type itself does not cause InStr to fail. However, Long Text fields frequently store pasted or imported data from external software like Microsoft Word, which automatically formats standard hyphens into en-dashes or em-dashes (Unicode characters), causing exact string matches to fail.

Excel & Data Problems