Solved

Query for a full name value in a single search field

Posted on 2008-06-11
6
156 Views
Last Modified: 2010-04-21
I am going to have a search field that just says 'Name'

The user can type in any value in the search field whether it be a full name, first, or last.

How can I create a optimal query for the value inputed.

Case 1 - If they type in John then I can say firstname like 'John%' or lastname like 'John%'
Case 2 - If they type in John Adams then I can return both values between spaces as separate values i guess firstname like 'john%' and lastname  like 'adams%'
Case 3 - If they type in Adams, John I guess I can look for a comma and the presume the value before the comma to be the last name and the value after to be the lastname.

These are pretty much all of the cases I could think of.  
Also would it be worth it to have the wildcard before the value as well like '%John%' instead of 'John%' ?
Please let me know has any ideas or if they have done something similar.


0
Comment
Question by:parikaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Author Comment

by:parikaa
ID: 21762325
Update to Case 3  I typed last name twice

Acutally case 3 should be:
Case 3 - If they type in Adams, John I guess I can look for a comma and the presume the value before the comma to be the last name and the value after to be the FIRSTNAME.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21762534
have:

Select FName, LName From Table1 Where Instr(FName &  LName, fnFName())>0 or  Instr(FName &  LName, fnLName())>0  

In a module have:

Function fnFName() as String
On Error GoTo 10
Dim strTemp As String

strTemp = Forms!MainForm!txtNameCriteria
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox  strTemp

 fnFName=fnFindName(strTemp , "FirstName")

Exit Function
10
fnFName="Not Valid"
End Function

Function fnFName() as String
On Error GoTo 10

strTemp = Forms!MainForm!txtNameCriteria
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox  strTemp

 fnFName=fnFindName(strTemp , "LastName")


Exit Function
10
fnFLame="Not Valid"
End Function

Function fnFindName(str_Temp As String, strNameType as String)As String
On Error GoTo 10

If Instr(str_Temp,",")> 0 Then
   If strNameType ="LastNAme" Then
       fnFindName= Left(str_Temp, InStr(str_Temp,",")-1)
   else
       fnFindName= Trim(Mid(str_Temp, InStr(str_Temp,",")+1))
  end if
Else
   If strNameType ="LastNAme" Then
       fnFindName= ""
   else
       fnFindName= Trim(str_Temp)
  end if
Else

End IF

Exit Function
10:
fnFindName=""
End Function

Mike
0
 
LVL 5

Author Comment

by:parikaa
ID: 21762770
Could you add some comments or make more clear? I can't follow what you are doing in the module.
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!

 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 21763344
Make a query and add your table to it. From this table select fields you want including FName LName

In the query, add two new fielda with alias name FirstAndLastName1 and  FirstAndLastName2 like:

FirstAndLastName1: Instr(FName &  LName, fnFName())

FirstAndLastName2: Instr(FName &  LName, fnLName())

Under FirstAndLastName1 in its criteria cell enter >0

Under FirstAndLastName2 in its criteria "SECOND LINE" cell enter >0

the criteria >0 should be staggred, each on one line to be interpreted as or.

Fileds like FirstAndLastName1 will have "JohnAdam" if either John or Adam is entered in the criteria box, "Instr(FName &  LName, fnFName())" produce number larger than 0.

The Function "fnFindName()" has two parameters. First users the entry from the criteria box in form of

John (as first or last name, only user knows but the code doesn't care because if the code sees John in JohnAdam's string it will show that name).

Possible entries:

John (as first or last name)
John John
John Adam
Adam, John
                                                   v--- brings in the one of above listed names from txtNameCriteria.
To continue with fnFindName(str_Temp, strNameType)
      function aske to recognize and return --^--- FName if strNameType= "FirstName" othewise ...LName...

' Replace following function:
Function fnFindName(str_Temp As String, strNameType as String)As String
On Error GoTo 10
               'v-- if a , detected, then it is in the form of Adam, John
If Instr(str_Temp,",")> 0 Then
   If strNameType ="LastNAme" Then             'v-- extracts Adam
       fnFindName= Left(str_Temp, InStr(str_Temp,",")-1)
   else                         'v-- extracts John
       fnFindName= Trim(Mid(str_Temp, InStr(str_Temp,",")+1))
  end if
Else  ' here we need some correction to what is alrady submitted

   If Instr(str_Temp," ")> 0 Then ' if it has space (already checked for ,) then it is like John Adam
       If strNameType ="LastNAme" Then
          fnFindName= Trim(Mid(str_Temp, InStr(str_Temp," ")+1)) ' gets Adam
   else
         fnFindName= Left(str_Temp, InStr(str_Temp," ")1)) ' gets John
  end if
               'v-- it is one piece John or Adam (both are taken a first and last name)
         fnFindName = str_Temp
End IF

Exit Function
10:
fnFindName=""
End Function

Copy and past the function to a module and run your query. Step though the code to make sure it is doing what it is supposed to do.

Mike


0
 
LVL 5

Author Closing Comment

by:parikaa
ID: 31466251
Thank you very much mike. This is great!
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21905232
You are most welcome. I see you have corrected few thngs which was wrong in the submitted code.

Thank you for the points and the grade.

Mke
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question