MS Access Query – Find Person by First and Last Name

ms access

I am using Microsoft Access. In my search form I have 3 text boxes that I enter text to search for: First Name; Last Name; Last 4 of SSN. With the current query when I enter text into multiple boxes it finds all the people with those names and/or last 4 of SSN. For example, I want to find Bill James with last 4 0111: If I enter text into all 3 boxes I end up with all the people that match my search parameters.

First Name      Last Name      Last4
Bill            Atherton       0123
Bill            James          0111
Bill            Wallace        0122
Frank           James          0101
Henry           James          0011

So you see I get all the people having a name of Bill and all the people having a name of James. I want to write a query so that if I enter Bill in the "First Name" text Box AND James in the "Last Name" text box I should only get people named Bill James not the rest of the people having either of those names. But I still want to be able to enter into one text box if I want to.

Here's my code so far:

SELECT Appointment.[First Name], Appointment.[Last Name], Appointment.DOB, Appointment.Last4
FROM Appointment
WHERE Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value 
            OR Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value
            OR Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value
ORDER BY [Last name], [First Name];

Best Answer

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer. In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)
Related Question