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

Query for a full name value in a single search field

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
parikaa
Asked:
parikaa
  • 3
  • 3
1 Solution
 
parikaaAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
parikaaAuthor Commented:
Could you add some comments or make more clear? I can't follow what you are doing in the module.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
parikaaAuthor Commented:
Thank you very much mike. This is great!
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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