How to Remove HTML Tags from an Access Long Text Field
Question details
The user needs to automate the removal of HTML tags (such as <div> and </div>) from a Long Text field in Microsoft Access without editing each record manually.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Cleaning up Long Text data that was converted from Rich Text and now displays leftover HTML markup.
- Observed behavior
- The Long Text field displays unwanted HTML tags directly in the text data instead of rendering or hiding them.
Always create a backup of your Access database before running any Update Queries, as these batch data modifications are permanent and cannot be undone.
Use an Update Query with the Replace Function
The most efficient way to remove specific HTML tags across all records is by running an Update Query with the nested Replace() function.
This method is ideal if you only have a few specific HTML tags (like <div> and </div>) that need to be cleared out. You can nest the Replace function to remove both the opening and closing tags in a single query execution.
Open your Access database, navigate to the 'Create' tab on the ribbon, and click 'Query Design'.
Select the table containing your Long Text field from the 'Show Table' dialog. Double-click the field (e.g., NoteDetail) to add it to the query design grid at the bottom.
In the 'Query Type' group on the ribbon, click 'Update'. This will add an 'Update To' row to your query design grid.
In the 'Update To' row under your Long Text field, enter the following expression to remove both tags: Replace(Replace([YourFieldName], "<div>", ""), "</div>", "")
Click the 'Run' button (the red exclamation mark) on the ribbon. Confirm the prompt warning you that you are about to update records.
Use a VBA Custom Function for Advanced HTML Cleanup
If your text contains many different or unpredictable HTML tags, a custom VBA function is better suited to strip all text between angle brackets.
Need a Free and Powerful Office Suite? Try WPS Office
While Microsoft Access is used for complex databases, most everyday data management, text cleanup, and reporting can be easily handled in spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office for your document, spreadsheet, and presentation needs.
Frequently Asked Questions
Why do I get a syntax error when using the Replace function in Access?
A common cause for syntax errors is forgetting to wrap the search string in quotation marks. For example, typing Replace([Field], <Div>, "") will fail. It must be written exactly as Replace([Field], "<div>", "").
Can I remove multiple different HTML tags at once in an Access query?
Yes, you can nest the Replace function to handle multiple tags in a single expression, such as Replace(Replace([FieldName], "<div>", ""), "</div>", ""). However, for heavily formatted HTML with many varying tags, using a VBA script to strip all tags is highly recommended.
Does converting Rich Text to Long Text automatically remove HTML?
No. When you convert a Rich Text field to a plain Long Text field in Access, the hidden HTML tags used for formatting (like <div> or <strong>) become visible plain text. You must manually clean them up using Update Queries or VBA functions.
