Others

Query a Comma-Separated List from an Access Form

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Match records against multiple 10-digit values entered in an Access form control.

Product
Microsoft Access
Device & OS
not provided
Scenario
A user enters several IDs separated by commas or spaces and wants to filter a query using that list.
Observed behavior
LIKE and IN expressions do not correctly match individual values in the form control because the list is treated as a single string.
Before you start

Identify the table field and the form control that contains the list, and make sure the form is open when you run the query. If IDs can begin with zero, store them as text so leading zeros are preserved.

Solution 1Recommended

Parse the form list with a public VBA function

Use this method to compare each field value with individual comma- or space-separated entries in the form control.

An Access IN expression does not automatically turn a form control's text into separate values. The function below splits the input and checks for an exact match.

1
Create a standard module

In Access, select Create > Module. Save the module with a name such as modListFilter; do not name it InParam.

2
Add the matching function

Paste this code into the module: Public Function InParam(ByVal fieldValue As Variant, ByVal listValue As Variant) As Boolean Dim parts() As String Dim item As Variant Dim listText As String If IsNull(fieldValue) Or IsNull(listValue) Then Exit Function listText = CStr(listValue) listText = Replace(listText, ",", " ") listText = Replace(listText, vbTab, " ") listText = Replace(listText, vbCr, " ") listText = Replace(listText, vbLf, " ") parts = Split(listText, " ") For Each item In parts If Len(item) > 0 Then If StrComp(CStr(fieldValue), CStr(item), vbTextCompare) = 0 Then InParam = True Exit Function End If End If Next item End Function

3
Add the query condition

Open the query in SQL View and add a condition such as WHERE InParam(Contacts.[ContactID], Forms!frmReportDialogue!txtContactIDList) = True. Replace the table, field, form, and control names with your own. Do not put quotation marks around True.

4
Test the filter

Open the form, enter values such as 1234567890, 2345678901 in the list control, and run the query. Only records with an exact matching ContactID should appear.

Blank lists and performance: This function returns False for a null or empty list, so no records match. Calling a VBA function for each row can be slow on large tables; for larger datasets, consider parsing and validating the list in the form module and building indexed field comparisons instead.
Free Microsoft Office alternative

Work with ID lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative for working with Office files through a familiar interface. Open compatible spreadsheets and migrate tabular data easily, although WPS does not run or repair Microsoft Access queries.

View and organize ID lists in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for tabular dataMove exported Access data into a lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't IN match the list in my form control?

The control supplies one text string, not a set of separate query values. Parse the string with a function such as InParam, then compare each token with the query field.

Can I separate IDs with spaces instead of commas?

Yes. The InParam function above accepts commas, spaces, tabs, and line breaks as separators. Enter the IDs in the form control before running the query.

Should True be in quotation marks in the query?

No. Use = True without quotation marks because True is a Boolean constant, not a text value.

How can I keep leading zeros in an ID?

Store the ID field as text and enter each ID with its leading zeros in the form control. A numeric field does not preserve leading zeros as part of its value.

Can I make the list filter optional?

Yes. In SQL View, you can add an OR condition that allows a null or empty control, for example: WHERE Nz(Forms!frmReportDialogue!txtContactIDList, '') = '' OR InParam(Contacts.[ContactID], Forms!frmReportDialogue!txtContactIDList) = True.