[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query either fields via text input and wildcards?

Posted on 2007-10-03
11
Medium Priority
?
236 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:DuarteR
  • 5
  • 5
11 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20010353
You need something like:
Where (Lastname =  txtLastname and txtFirstName = "")
OR  (Firstname =  txtFirstname and txtLastName = "")
OR  (Lastname =  txtLastname and Firstname = txtFirstName )


0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20010648
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] & "*"))) OR (((tblSearches.firstname) Like ("*" & [Forms]![Finder]![txtInput] & "*")));
0
 

Author Comment

by:DuarteR
ID: 20016219
Sorry but where do I type these codes into?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20016753
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
0
 

Author Comment

by:DuarteR
ID: 20016991
thanks for the help puppydogbuddy.

Is there no way to have seperate input text boxes to search seprately first and last names in the same query?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20017123
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]![txtInput1] & "*"))) OR (((tblSearches.firstname) Like ("*" & [Forms]![Finder]![txtInput2] & "*")));
0
 

Author Comment

by:DuarteR
ID: 20018358
That last one does not seem to work - it gives me all the records.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 750 total points
ID: 20018379
Try changing the Or to an And
change this:
HAVING (((tblSearches.lastname) Like ("*" & [Forms]![Finder]![txtInput1] & "*"))) OR

to:
HAVING (((tblSearches.lastname) Like ("*" & [Forms]![Finder]![txtInput1] & "*"))) And
0
 

Author Comment

by:DuarteR
ID: 20022944
Did'nt work as I wanted but I will go with just the one searching by either name in the one input box.

thanks
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 20023389
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]![txtInput1] & "*") & "," & ("*" & [Forms]![Finder]![txtInput2] & "*")))
ORDER BY [lastname] & "," & [firstname];
0
 

Author Comment

by:DuarteR
ID: 20023675
That works exactly right! thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question