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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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..
Jeffrey CoachmanMIS LiasonCommented:
Where are these "results" being displayed?
urjudoAuthor Commented:
@Jeffrey coachman:  The results being displayed in a form.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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) & "*"));'
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

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) & "*");
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

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 & "*";

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
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

urjudoAuthor Commented:
Thanks for help!!!
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.