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

[atadbitslow]

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!

 

[Andrzejek]

If your table is tblMyTable with a field named Organization and you type your search text in txtSearch textbox, your SQL may look like:
[tt]
strSQL = "Select * From tblMyTable " & _
" Where UCase(Organization) Like '*" & UCase(txtSearch.Text) & "*'"

Debug.Print strSQL
[/tt]

---- Andy

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

 

[atadbitslow]

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

 

[Andrzejek]

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

Basic, short code for the button:

[blue]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[/blue]

If you run it, type [red]corn[/red] in the box, and click on Search button, in the Immediate Window you will get:[tt][blue]
Select * From [admissions outreach] Where UCase(ORGANIZATION) Like '*CORN*'[/tt]
[/blue]
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

 

[dhookom]

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.

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

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

 

[atadbitslow]

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!