Query a Comma-Separated List from an Access Form
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.
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.
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.
In Access, select Create > Module. Save the module with a name such as modListFilter; do not name it InParam.
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
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.
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.
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.

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.




