Fix Microsoft Access Error 3075 in a Multi-Field Search Query
Question details
Correct a Microsoft Access VBA search query that searches several fields, including a multivalued field.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A VBA search query searches directory fields whose names include spaces and a multivalued Title field.
- Observed behavior
- The query raises run-time error 3075. A revised query can also raise error 3831 when the multivalued field is used directly in a WHERE clause.
Open the directory table in Design View and confirm the exact field names, the primary key, and whether Title is multivalued. In the VBA editor, locate the code that builds and runs the search SQL.
Correct field references and search multivalued titles through a related table
Use this approach to fix the SQL syntax and avoid placing the complex multivalued field directly in the search criteria.
Field names containing spaces need square brackets. The .Value qualifier is not appropriate for ordinary text fields, and a multivalued field cannot be treated like an ordinary text field in a standard WHERE clause.
For a reliable search across titles, use a related table with one title per row, linked to the directory record. Adapt the example names below to your actual tables and keys.
In the VBA editor, update the SQL string to use names such as [Last Name], [Facility Name], and [Organization Name]. Remove .Value from any ordinary text field reference.
In Access, use Create > Table Design to create or identify a related table with a directory key and a text column for individual titles. Link its directory key to the directory table's primary key; ensure the title values you need to search are present as separate rows.
Open Create > Query Design, switch to SQL View, and adapt this example: PARAMETERS [SearchText] Text (255); SELECT d.[DirectoryID], d.[Last Name], d.[Facility Name], d.[Organization Name] FROM Tbl_GeneralDirectory AS d WHERE d.[Last Name] LIKE '*' & [SearchText] & '*' OR d.[Facility Name] LIKE '*' & [SearchText] & '*' OR d.[Organization Name] LIKE '*' & [SearchText] & '*' OR EXISTS (SELECT * FROM DirectoryTitles AS t WHERE t.[DirectoryID] = d.[DirectoryID] AND t.[TitleText] LIKE '*' & [SearchText] & '*'); Replace DirectoryID, DirectoryTitles, and TitleText with your actual key, table, and text field names.
Run the saved query in Access and enter a test term when prompted. In VBA, open the saved QueryDef and set its SearchText parameter to strText before opening its recordset, rather than concatenating user input into the SQL string.
Work with directory data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents. Move suitable exported directory data into a spreadsheet for filtering and review; WPS does not repair or run the Microsoft Access query.

Frequently Asked Questions
Why does Access show error 3075 for a field name with spaces?
Access may interpret an unbracketed name as separate SQL tokens. In the query's SQL View or VBA SQL string, write names such as [Last Name] and [Facility Name].
Why do I get error 3831 after fixing the field names?
Error 3831 can occur when a multivalued field is used directly in a WHERE or HAVING clause. Search individual values through an appropriate related value table or a properly joined multivalue subtable instead.
Should I use .Value on every field in the search?
No. Remove .Value from ordinary text fields. A multivalued field requires special handling and should not be assumed to behave like an ordinary text column.
How can I search for a name containing an apostrophe?
Use a parameterized saved query and assign the search text to its parameter in VBA. If you construct a single-quoted SQL text literal instead, double each apostrophe in the input.
Which wildcard should I use in an Access LIKE search?
Use * for an Access database using ANSI-89 syntax, as in LIKE '*' & [SearchText] & '*'. Use % when the database uses ANSI-92 syntax.




