asked on

SQL query that concanate 2 fields for 1 search

Hi experts,
I have a database that have these 2 fields, name and firstname, and I want to do a query from one field that MIGHT INCLUDE both fields or JUST ONE
EX.
Name FirstName
1 John Doe
2 George Wayne
3 John Doe
4 George Wayne

I would like to search for John, and get record 1 and 3
Search John Doe and get record 1 and 3
Search Doe and get record 1 and 3

What I am able to do with the code below is : If I search John Doe, I will only get record 3, but I want to be able to get record 1 TOO !!! (Doe is part of FirstName)

strSQl = "SELECT name, firstname FROM customers WHERE (name LIKE '*" & dt(0).Text & "*') OR (firstname LIKE '*" & dt(0).Text & "*')"
strSQl = strSQl + " order by main"

*** Also what would I need to insert IF I DON'T WANT TO INCLUDE DUPLICATES

Thanx

jerryb30

What exactly do you want to see from the data posted?
Is dt(0).Text {first name] or [name]?

crescue

ASKER

dt(0) is the input field that I would like to search from
dt(0) might be JOHN or DOE or JOHN DOE
I want to use that dt(0).text to look into {first name] or [name]

jerryb30

I have that.
So you want to see
John Doe
once

This is without criteria.

SELECT Trim(nz([firstName],"") & " " & Replace([name],nz([firstname],""),"")) AS Expr1
FROM Customers
GROUP BY Trim(nz([firstName],"") & " " & Replace([name],nz([firstname],""),""));
Assumes no blank [name]

jerryb30

This is fraught with possibilities for errors, since repeats of first and last name could cause some records not to show.

jerryb30

Oh, and I did the one for no duplicates.
Is your criteria working otherwise?

mbizup

I think this will do the trick too:

strSQL = "SELECT [Name] & ' ' & FirstName FROM Customers WHERE [Name] & ' ' & FirstName  LIKE  '*" & dt(0).Text & "*'"

Open in new window

Not sure how you are defining 'duplicates'. Are rows 1 and 3 duplicates, or are they different?

crescue

ASKER

rows 1 and 3 are different
Nope, it is not working the way I want it to work (according to the code provided)
Basically what I want is :

There is NO 'JOHN DOE' EITHER IN [NAME] nor [FIRSTNAME]
There is 'DOE' in [FIRSTNAME] and 'JOHN' in [NAME]

**** But I want to search for 'JOHN DOE' and STILL GET IT

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

btw, what are you using for your code? Access VBA, VB.Net, VBScript...?

dt(0).Text looks unusual for Access VBA.

crescue

ASKER

awking00

strSQl = "SELECT name, firstname FROM customers WHERE instr(name & " " & firstname,dt(0).Text) > 0

jerryb30

try:
SELECT Customers.firstName, Customers.Name, Customers.Main
FROM Customers
WHERE (((Trim(nz([firstName],"") & " " & Replace(nz([name],""),nz([firstname],""),""))) Like "*" & [entername] & "*")) OR (((Customers.firstName) Like "*" & [entername] & "*")) OR (((Customers.Name) Like "*" & [entername] & "*"))
ORDER BY Customers.Main;
where [entername] is your criteria

crescue

ASKER

Thqnx