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.
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.
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!txtNameCrit
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox strTemp
fnFName=fnFindName(strTemp
Exit Function
10
fnFName="Not Valid"
End Function
Function fnFName() as String
On Error GoTo 10
strTemp = Forms!MainForm!txtNameCrit
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox strTemp
fnFName=fnFindName(strTemp
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
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!txtNameCrit
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox strTemp
fnFName=fnFindName(strTemp
Exit Function
10
fnFName="Not Valid"
End Function
Function fnFName() as String
On Error GoTo 10
strTemp = Forms!MainForm!txtNameCrit
'remove the message box after making sure it can read the value in txtNameCriteria you have
MsgBox strTemp
fnFName=fnFindName(strTemp
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
ASKER
Could you add some comments or make more clear? I can't follow what you are doing in the module.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much mike. This is great!
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
Thank you for the points and the grade.
Mke
ASKER
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.