Search Across Two Concatenated Fields

[e_marquess]

This should be simple but it has me stumped.

I have two fields First_Name and Last_Name. I want to search for John
Smith by entering "John", "Smith" or "John Smith". I have tried to
concatenate the fields with this code and it works with "John" and
"Smith" but comes up blank when I enter "John Smith".

SELECT First_Name, Last_Name FROM ASPP_Users WHERE (First_Name + ' ' +
Last_Name) Like'%" + Replace(Recordset1__r_name, "'", "''") + "%'

Is there something incorrect in the concatenation part of the code ? I
am using MS Access.

[e_marquess]

It's OK now. After many hours I got it to work.

For anyone else who might need it, drop the space inside ' ' and use ''
instead.

So the correct code is :

SELECT First_Name, Last_Name FROM ASPP_Users WHERE (First_Name + '' +

Last_Name) Like'%" + Replace(Recordset1__r_name, "'", "''") + "%'

e_********@hotmail.com wrote:

This should be simple but it has me stumped.

I have two fields First_Name and Last_Name. I want to search for John
Smith by entering "John", "Smith" or "John Smith". I have tried to
concatenate the fields with this code and it works with "John" and
"Smith" but comes up blank when I enter "John Smith".

SELECT First_Name, Last_Name FROM ASPP_Users WHERE (First_Name + ' ' +
Last_Name) Like'%" + Replace(Recordset1__r_name, "'", "''") + "%'

Is there something incorrect in the concatenation part of the code ? I
am using MS Access.