Solved

Query for a full name value in a single search field

Posted on 2008-06-11
6
153 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

785 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