×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how to search data and display matches that include the user's input, but not necessarily match

how to search data and display matches that include the user's input, but not necessarily match

how to search data and display matches that include the user's input, but not necessarily match

(OP)
Hi,
I have a form that I'd like users to be able to enter a partial word/string of letters and records that include the partial word/string of letters will display. For example, the search is to be done on a field called 'organization'. Needing a user to type in the exact way an organization may be entered in the database would not be helpful. Some organizations may start with 'The' or may end with 'Organization' etc. I'd like the user to be able to type in 'Corn' and have the program find 'Cornwell' or any other organizations that have 'corn' in their name. How would I go about that?
Thanks!

RE: how to search data and display matches that include the user's input, but not necessarily match

If your table is tblMyTable with a field named Organization and you type your search text in txtSearch textbox, your SQL may look like:

strSQL = "Select * From tblMyTable " & _
" Where UCase(Organization) Like '*" & UCase(txtSearch.Text) & "*'"

Debug.Print strSQL


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: how to search data and display matches that include the user's input, but not necessarily match

(OP)
Hi,
Thank you very much for your reply. I'm too much of a beginner to know what to do with that statement in regards to the form I have. I tried to create a query but it didn't produce any results even though there are organizations that have the string I entered into the search box.... unfortunately, I'm lost!
SELECT *
FROM [admissions outreach]
WHERE (((UCase([ORGANIZATION])) Like '*" & Ucase(me.searchtext) & "*'));

RE: how to search data and display matches that include the user's input, but not necessarily match

OK, so it is a simple Form. one textbox named txtSearchText and a command button named cmdSearch

Basic, short code for the button:

CODE

Option Explicit

Private Sub cmdSearch_Click()
Dim strSQL As String

strSQL = "Select * From [admissions outreach] " & _
    " Where UCase(ORGANIZATION) Like '*" & UCase(txtSearchText.Text) & "*'"

Debug.Print strSQL

End Sub 
If you run it, type corn in the box, and click on Search button, in the Immediate Window you will get:
Select * From [admissions outreach] Where UCase(ORGANIZATION) Like '*CORN*'


Run this Select statement in your Access to see the outcome.
How you want to display this data on your Form is up to you.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: how to search data and display matches that include the user's input, but not necessarily match

You would not want to use the “.Text” property since it is only viable when the control has the focus. I typically don’t worry about upper or lower case.

CODE --> Code

strSQL = "Select * From [admissions outreach] " & _
    " Where UCase(ORGANIZATION) Like '*" & UCase(txtSearchText) & "*'" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: how to search data and display matches that include the user's input, but not necessarily match

(OP)
Thanks you both very much! I appreciate your help and I hope to get to the project today. I'll let you know if I am successful at getting it to work! Thanks again!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close