Solved

Query for a full name value in a single search field

Posted on 2008-06-11
6
151 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
  • 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 33

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 33

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 33

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now