Others

How to Search an Access Database by a Person's Full Name

Anum MushtaqAnum Mushtaq Nov 4, 2026 868 views

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.

How to Search an Access Database by a Person's Full Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Query Design

Navigate to the 'Create' tab on the top ribbon and select 'Query Design' to start a new query.

2
Add the Contacts Table

Select the table containing your contact records from the 'Show Table' dialog and click 'Add', then close the dialog.

3
Create the Concatenated Field

In a blank query column, type the following expression to merge the names: FullName: [FirstName] & " " & [LastName].

4
Set Search Criteria

In the 'Criteria' row beneath your new field, type Like "*" & [Enter Name to Search] & "*" to prompt the user for a search parameter.

5
Run and Test

Click 'Run' on the Query Tools Design tab. Enter a full name when prompted to verify that the correct record is returned.

Create a Concatenated Query for Full Name Search
Database Normalization: Relying on a query to dynamically combine names ensures your database remains properly normalized by avoiding redundant data columns.
Free Microsoft Office alternative

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. 1. Export Your Data: Export your current Access contact tables as a standard Excel (.xlsx) file.
  2. 2. Open in WPS Spreadsheet: Launch WPS Spreadsheet and open the exported file to view your database records.
  3. 3. Merge Fields Easily: Use the built-in CONCATENATE function to instantly merge your first and last name columns for easy searching.
Highly compatible with Microsoft Excel (.xlsx, .xls) and standard CSV file formats.Lightweight software design ensuring fast installation and smooth operation on any device.Advanced data filtering, built-in concatenation formulas, and pivot tables for easy data management.Completely free to download with an intuitive, familiar user interface requiring no learning curve.
microsoft office alternative - wps office

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].