Search Firstname field AND Lastname field?

[Guest]

I am learning Microsoft Access. I have created a very simple table named
"presidents_tbl".

I have constructed a query that uses the "like" statement that will find any
president in the table by typing just part of a president's first name into a
dialog box.

I would like to have Access also look at the last name field for matches
that I type into the dialog box. For example if I type "John" into the
dialog box Access would then return "John Kennedy" and "Lyndon Johnson".

If I can get this to work I will then be on to bigger and better things
using this knowledge, but for now I am stuck.

Here is my SQL code so far...

SELECT presidents_tbl.firstname, presidents_tbl.lastname
FROM presidents_tbl
WHERE (((presidents_tbl.firstname) Like '*' & [] & '*'));

From what I have seen from my Google searches, it looks like I must just put
in a comma somewhere and add "lastname" to the where statement but I can not
get anything to work. (I could not get "OR" to work either)

How do I get Microsoft Access to include both the firstname field and the
last name field in my query?

 

[Douglas J. Steele]

Try:

SELECT presidents_tbl.firstname, presidents_tbl.lastname
FROM presidents_tbl
WHERE presidents_tbl.firstname Like '*' & [First Name] & '*'
AND presidents_tbl.lastname Like '*' & [Last Name] & '*'

 

[John Vinson]

How do I get Microsoft Access to include both the firstname field and the
last name field in my query?

The WHERE clause is a Boolean logical expression; if it evaluates to
TRUE, a record is returned; if it evaluates to FALSE it isn't.

You can use the AND operator to require that two conditions must both
be true in order to retrieve a record, or use the OR operator to
retrieve the record if either one of two conditions is true:

SELECT presidents_tbl.firstname, presidents_tbl.lastname
FROM presidents_tbl
WHERE (((presidents_tbl.firstname) Like '*' & [] & '*'))
OR (((presidents_tbl.lastname) Like '*' & [] & '*'))


Note that I did learn something from your question: I had no idea you
could use an empty parameter! Typically I'd have used [Enter part of
the name:] or some other prompt.

John W. Vinson[MVP]

 

[Guest]

John provided exactly the answer I needed. I copied and pasted his code
right into Access and it worked perfectly and now I can see why it works! I
also tried the post by Douglas and his answer was right except he needed to
use "OR" instead of "AND".

When you use "AND" it will only give a result if you have a president with a
first name like "John" and a last name like "John. Both names have to be
John!

When I use "OR" I get the desired result.

John... George Boole would be proud of you!

I should have known that someone named "John" would know how to get the
"John" string out of the former presidents table!

How do I get Microsoft Access to include both the firstname field and the
last name field in my query?

The WHERE clause is a Boolean logical expression; if it evaluates to
TRUE, a record is returned; if it evaluates to FALSE it isn't.

You can use the AND operator to require that two conditions must both
be true in order to retrieve a record, or use the OR operator to
retrieve the record if either one of two conditions is true:

SELECT presidents_tbl.firstname, presidents_tbl.lastname
FROM presidents_tbl
WHERE (((presidents_tbl.firstname) Like '*' & [] & '*'))
OR (((presidents_tbl.lastname) Like '*' & [] & '*'))


Note that I did learn something from your question: I had no idea you
could use an empty parameter! Typically I'd have used [Enter part of
the name:] or some other prompt.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Actually, mine assumed you would type in separate first names and last
names. I read too quickly, and missed the fact that you only wanted to type
in only one name, and use it in either the first or last name (despite the
fact that your example illustrated that point). And like John, I didn't
realize that you could put strictly [], although I'm not sure that's a good
idea: having a prompt is always useful!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John provided exactly the answer I needed. I copied and pasted his code
right into Access and it worked perfectly and now I can see why it works!
I
also tried the post by Douglas and his answer was right except he needed
to
use "OR" instead of "AND".

When you use "AND" it will only give a result if you have a president with
a
first name like "John" and a last name like "John. Both names have to be
John!

When I use "OR" I get the desired result.

John... George Boole would be proud of you!

I should have known that someone named "John" would know how to get the
"John" string out of the former presidents table!

How do I get Microsoft Access to include both the firstname field and
the
last name field in my query?

The WHERE clause is a Boolean logical expression; if it evaluates to
TRUE, a record is returned; if it evaluates to FALSE it isn't.

You can use the AND operator to require that two conditions must both
be true in order to retrieve a record, or use the OR operator to
retrieve the record if either one of two conditions is true:

SELECT presidents_tbl.firstname, presidents_tbl.lastname
FROM presidents_tbl
WHERE (((presidents_tbl.firstname) Like '*' & [] & '*'))
OR (((presidents_tbl.lastname) Like '*' & [] & '*'))


Note that I did learn something from your question: I had no idea you
could use an empty parameter! Typically I'd have used [Enter part of
the name:] or some other prompt.

John W. Vinson[MVP]