• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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
0
crescue
Asked:
crescue
  • 5
  • 4
  • 3
  • +1
1 Solution
 
jerryb30Commented:
What exactly do you want to see from the data posted?
Is dt(0).Text {first name] or [name]?
0
 
crescueAuthor Commented:
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]
0
 
jerryb30Commented:
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]
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jerryb30Commented:
This is fraught with possibilities for errors, since repeats of first and last name could cause some records not to show.
0
 
jerryb30Commented:
Oh, and I did the one for no duplicates.
Is your criteria working otherwise?
0
 
mbizupCommented:
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?
0
 
crescueAuthor Commented:
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
0
 
mbizupCommented:
Okay...

Try this:

dim s()
dim x
dim strSQL
dim strWhere
dim strRoot

s = split(dt(0).Text, " ")
strRoot = " [Name] & ' ' & FirstName "

for each x in s
       strWhere = strWhere & strRoot & "LIKE '*" & x  & "*' OR"
Next
if Right( "nn" & strWhere, 2) = "OR" then strWhere = Left(strWhere, Len(strWhere) - 2)
strSQL = "SELECT [Name] & ' ' & FirstName FROM Customers WHERE " & strWhere

Open in new window

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

dt(0).Text looks unusual for Access VBA.
0
 
crescueAuthor Commented:
VB
0
 
awking00Commented:
strSQl = "SELECT name, firstname FROM customers WHERE instr(name & " " & firstname,dt(0).Text) > 0
0
 
jerryb30Commented:
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
0
 
crescueAuthor Commented:
Thqnx
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now