How to Search an Access Database by a Person's Full Name
Question details
Users want to search for a contact in an Access database using their full name format without having to store a redundant, combined name field.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Searching for specific records using a full name (e.g., "John Doe" or "Doe, John") when the first and last names are stored in entirely separate database columns.
- Observed behavior
- The database natively searches first and last names as independent fields, prompting users to find a method to dynamically combine and query these inputs seamlessly.
Ensure you have Design View access to your database forms and queries. Note the exact field names used in your tables for the first and last names.
Create a Concatenated Query for Full Name Search
Combine the first and last name fields into a single expression within a query to allow seamless full name searching across the database.
By dynamically concatenating the name fields in a query, you can execute searches against a full name string without duplicating data in your tables.
Navigate to the 'Create' tab on the top ribbon and select 'Query Design' to start a new query.
Select the table containing your contact records from the 'Show Table' dialog and click 'Add', then close the dialog.
In a blank query column, type the following expression to merge the names: FullName: [FirstName] & " " & [LastName].
In the 'Criteria' row beneath your new field, type Like "*" & [Enter Name to Search] & "*" to prompt the user for a search parameter.
Click 'Run' on the Query Tools Design tab. Enter a full name when prompted to verify that the correct record is returned.

Implement a Combo Box for Name Selection
Use a combo box on your form with a customized RowSource to help users find and jump to specific records as they type.
Use Separate Search Controls for First and Last Names
Because people often have multi-word first or last names, filtering fields separately is often the most accurate and reliable method.
Manage Your Data Efficiently with WPS Office
While Microsoft Access is a powerful database tool, many data management, filtering, and reporting tasks can be seamlessly handled using WPS Spreadsheet. WPS Office is a free, lightweight suite that offers robust data analysis tools with a highly familiar user interface.
- 1. Export Your Data: Export your current Access contact tables as a standard Excel (.xlsx) file.
- 2. Open in WPS Spreadsheet: Launch WPS Spreadsheet and open the exported file to view your database records.
- 3. Merge Fields Easily: Use the built-in CONCATENATE function to instantly merge your first and last name columns for easy searching.

Frequently Asked Questions
Can I just add a 'Full Name' field to my table instead?
It is not recommended to add a redundant Full Name field. Doing so violates database normalization rules because it duplicates data already stored in the First Name and Last Name fields, which can lead to data inconsistencies if a name changes.
How do I handle searching for names with multiple words like 'Mary Jane'?
Because splitting a single search string into separate first and last names via parsing is highly error-prone for multi-word names, it is best to provide two distinct input boxes on your search form (one dedicated to the first name, one to the last name).
Can a combo box distinguish between two contacts with the exact same name?
Yes. To differentiate contacts with identical names, you can modify the combo box's RowSource query to concatenate additional identifying information, such as adding the user's city or department: FullName: [LastName] & ", " & [FirstName] & " - " & [City].




