Using Like to filter a Query

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Using Like to filter a Query

Post by Leesha »

Hi,
I'm using the code below to filter a query in a listbox based on a text box on a form. It works OK if the entire word is put in the search box. What I need it to do is to filter no matter how much is typed in and pull up anything that is close. For example if the person types in "abdominal" all works with abdominal would come up.


Thanks!
Leesha

Like IIf(IsNull([forms]![frmClientMedicalDiagnoses]![txtSearch]),"*",[forms]![frmClientMedicalDiagnoses]![txtSearch])

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using Like to filter a Query

Post by HansV »

Try

Like "*" & [Forms]![frmClientMedicalDiagnoses]![txtSearch] & "*"

If you want to include records where the field is empty if the text box is empty, use

Like "*" & [Forms]![frmClientMedicalDiagnoses]![txtSearch] & "*" Or [Forms]![frmClientMedicalDiagnoses]![txtSearch] Is Null
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Using Like to filter a Query

Post by Leesha »

Thanks Hans! Worked like a charm!
Have a great day,
Leesha

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Using Like to filter a Query

Post by Leesha »

Back again! Is there a way to stop access from autocorrecting a text box? When I type in "acn" in the search box above it changes it to "can".
Thanks!

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using Like to filter a Query

Post by HansV »

That is a user-level setting, not a database-level setting.
Select File > Options > Proofing, then click AutoCorrect Options...
Clear the check box 'Replace text as you type', then OK your way out.

S1235.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Using Like to filter a Query

Post by Leesha »

Thanks! I greatly appreciate it!