ACC2002: Filter By Form Does Not Filter Data That Has Commas in It (304523)

Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you view a table in Datasheet view, filter by form does not filter data that has commas in it if all of the following conditions are true:

The display control is a combo box.
The bound column is hidden.
The displayed column is concatenated with data that has a comma in it.

Additionally, you receive the following error messages:

The expression you entered contains invalid syntax, or you need to enclose your text data within quotes.

Microsoft Access didn't build the filter.

RESOLUTION

To find the records that have commas in them, enclose the data that you want to filter by in quotation marks.

For example, change Buchanan, Steven to "Buchanan, Steven" in the Steps to Reproduce the Behavior section to see all the records about Steven Buchanan.

MORE INFORMATION

Steps to Reproduce the Behavior

Start Access.
On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
In the Database window, click Tables under Objects, click the Orders table, and then click Open.
On the Records menu, point to Filter, and then click Filter By Form.
Click in the empty row cell under Employee, click the arrow, and then click Buchanan, Steven.
On the Filter menu, click Apply Filter/Sort.

Note that you receive the error message that is mentioned in the "Symptoms" section of this article.