Query question

Hi Experts,
I have a query question.  I have a form that for users to search client's name, when the user enter the client's last name, it pull all the last name that the user search with all the first name start with A.  For example, when user search for Smith, Mary.  User enter "Smith" then the form list all the last name "Smith" with start from Ann, Anna to Zee etc, now we want to make a little change that if user enter "Smith, Ma" then only shows last name "Smith" and all the first name with "ma" no other names after "ma" that way is much easier for user to search.  Below is the query for the form:

SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME, Left([RINLNAME],2) AS Expr1
FROM vqryCPCaseInfo
WHERE (((Left([RINLNAME],2)) Like Left([forms]![frmFindRecords]![FindField],2) & "*"));

I have tried this:
SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME, [RIN]![[RINLANME] & ", " & Left([RINFNAME],2) AS Expr1
FROM vqryCPCaseInfo
WHERE ((([RIN]![[RINLANME] & ", " & Left([RINFNAME],2)) Like Left([forms]![frmFindRecords]![FindField],2) & "*"));
but it does not work. Any idea?

Thanks for help
urjudoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
This should work:

SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME 
FROM vqryCPCaseInfo
WHERE 
(([RINLNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(0) & "*"))
AND
(([RINFNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(1) & "*"));

Open in new window


EDIT: This would essentially allow you to search for "Mary Smith" by entering something as short as "Sm,Ma", as it just splits up what is entered and uses the two components in partial matches for last name and first name, respectively.   This may not be what you want because "Matthew Small" would appear before Mary Smith.  An alternative is to use a combo-box that contains all of the names from the table in a concatenated LastName, FIrstName column along with record ID as the bound column... this would allow for finding a specific single record by typing last name followed by as many characters of first name as needed to hone in on the specific record..
0
Jeffrey CoachmanMIS LiasonCommented:
Where are these "results" being displayed?
0
urjudoAuthor Commented:
@Jeffrey coachman:  The results being displayed in a form.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

urjudoAuthor Commented:
@Robert Sherman: I tried your suggestion, but I got an error message "extra ) in query expression '(([RINLNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(0) & "*"))
AND
(([RINFNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(1) & "*"));'
0
Robert ShermanOwnerCommented:
Here ya go:

SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME 
FROM vqryCPCaseInfo
WHERE 
(([RINLNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(0) & "*")
AND
(([RINFNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(1) & "*");

Open in new window

0
urjudoAuthor Commented:
@Robert sherman: error message "invalid use of '.','!',or'()'. (([RINLNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(0) & "*")
AND
(([RINFNAME]) Like Split([forms]![frmFindRecords]![FindField], ",")(1) & "*");
0
Robert ShermanOwnerCommented:
OK, sorry about that.  Turns out what I was attempting was too much to do inside a SQL statement.

A workaround, if you're willing to indulge me a bit longer, would be to add a function in a VBA code module like this:

Function GetPart(inString As String, partNumber As Integer) As String

GetPart = Split(inString, ",")(partNumber)

End Function

Open in new window


And then change the SQL to:

SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME 
FROM vqryCPCaseInfo
WHERE 
(([RINLNAME]) Like GetPart([forms]![frmFindRecords]![FindField], 0) & "*")
AND
(([RINFNAME]) Like GetPart([forms]![frmFindRecords]![FindField], 1) & "*");

Open in new window

0
PatHartmanCommented:
Use two separate search fields.  One for last name and one for first name.  Then the user can use either or both.  By using only one field, you are overly complicating the process.

SELECT vqryCPCaseInfo.RINLNAME, vqryCPCaseInfo.RINFNAME
 FROM vqryCPCaseInfo
 WHERE [RINLNAME] Like forms]![frmFindRecords]![LName] & "*"
AND [RINFNAME] Like forms!frmFindRecords![FName & "*";
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Try this sample as a test
If it does what you like, ...we can tweak it, ...if needed...
Database110.mdb
0
Robert ShermanOwnerCommented:
@PatHartman has a very good point which you should consider.

If you decide to stick with the single field approach, I offer this updated version of the function I posted above.  I did not take into consideration entering only last name for search, so the previous version would produce an error if there was no comma in the search input.

Function GetPart(inString As String, partNumber As Integer) As String

arr = Split(inString, ",")

If partNumber > UBound(arr) Then
    GetPart = ""
Else
    GetPart = arr(partNumber)
End If

End Function

Open in new window

0
urjudoAuthor Commented:
Thanks for help!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.