Newbie question:
I have a very simple table with four fields: lastname, firstname, mp and ext and I have a form for searching these records with two input text boxes for lastname and firstname.
I have managed to work it so if I type say Smith into the lastname input text box and click a button a query will show all the records with the last name Smith.
I want it to work so I can either type in the surname or the firstname input box to give me query results but without having to have seperate queries and buttons e.g serach by lastname, search by first name.
Also how can I make it take wildcards eg. if I type sm it will give me smith, small etc?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
1. have only one textbox for input, named txtInput on a form I named "Finder" for illustrative purposes.
2. create a button to run the following query against your table (assumed name is tblSearches_ after something entered in txtInput
SELECT tblSearches.lastname, tblSearches.firstname
FROM tblSearches
GROUP BY tblSearches.lastname, tblSearches.firstname
HAVING (((tblSearches.lastname) Like ("*" & [Forms]![Finder]![txtInput
You can paste the select statement in the sql view of a query, save the query, and then use the query in a button click event procedure like this:
Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
Dim stDocName As String
stDocName = "qrySearches"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_btnSearch_Click:
Exit Sub
Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click
End Sub
Yes, modify the select statement for two input boxes, txtInput1 and txtInput2 As Shown:
SELECT tblSearches.lastname, tblSearches.firstname
FROM tblSearches
GROUP BY tblSearches.lastname, tblSearches.firstname
HAVING (((tblSearches.lastname) Like ("*" & [Forms]![Finder]![txtInput
you need to tell me it did not work. this should give you what you wanted. Let me know.
SELECT [lastname] & "," & [firstname] AS fullname, tblSearches.lastname, tblSearches.firstname
FROM tblSearches
GROUP BY [lastname] & "," & [firstname], tblSearches.lastname, tblSearches.firstname
HAVING ((([lastname] & "," & [firstname]) Like ("*" & [Forms]![Finder]![txtInput
ORDER BY [lastname] & "," & [firstname];
Business Accounts
Answer for Membership
by: MikeToolePosted on 2007-10-03 at 14:53:44ID: 20010353
You need something like:
Where (Lastname = txtLastname and txtFirstName = "")
OR (Firstname = txtFirstname and txtLastName = "")
OR (Lastname = txtLastname and Firstname = txtFirstName )