Solved

Find Partial Text match in MS Access Query

Posted on 2009-05-06
8
4,019 Views
Last Modified: 2013-11-27
Hello!

I have a query by form function that calls a report.  The form is pretty straightforward...about a dozen drop-downs with static possibilities.  The form is connected to a query that uses the following formula to handle all the query possibilities:

IIf(IsNull([Forms]![frmSearch]![XXXXX]),[XXXXX],[Forms]![frmSearch]![XXXXX])

The query is the basis for a report.  

Everything works great, but I would also like to search a "notes" field by keyword.  The notes fields, as you might imagine, are paragraphs full of info.  I would like to be able to search the notes field by a single word or partial word, so that a search on "dog" would find "dogma" and "dogs" (and to be clear, search for the text anywhere in the notes field, even in the middle of a word).  How can I do this using the above formula?  Where do I put the "like" or "*" or whatever I 'm guessing I will need?  The name of the field is "Notes" BTW.

Thank you!
0
Comment
Question by:matthewlorin7
  • 4
  • 3
8 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24319177
In the above formula, do the [XXXXX]'s all refer to the same control? That is to say, if you have a combo box names 'cboName', would the above formula be:

IIf(IsNull([Forms]![frmSearch]![cboName]),[cboName],[Forms]![frmSearch]![cboName])

Would it be possible to see the whole query?
A lot depends on how many records you are processing. Using a wild card search on a larger text field can be very time-consuming. There are some alternate ways to do that kind of a search in a query (e.g. you can write your own function and use it in a query).
Todd
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24320162
What is [XXXXX]?  A little less cryptic please.
0
 

Author Comment

by:matthewlorin7
ID: 24320286
shambalad:  Thank you for the quick reply.  The formula references two items: the combobox from the form I am using to select my search parameter, and within the query itself.  Confusingly, they have the same name (Notes).  A better way would be to have them labeled differently (frmSearchNotes and qryNotes, perhaps).  Regardless, basically the IIF is saying that if the "Notes" field in the search form is empty, then default to the "Notes" field from the query (which will include every possible record).  Otherwise, choose the "Notes" field from the Search form (which will filter for that term).  This is how it works for all the other fields on the search form, anyway (I have about a dozen).  However, I would like to search the entire paragraph of the "Notes" field by a single word and if that word is contained in the field (anywhere), I would like it reflected in my report.  Also, I need it to be an IIF funtion to account for when there is nothing entered in the "Notes" field on the search form.  This is what I've come up with below, but it does not work:

IIf(IsNull([Forms]![frmSearch]![Notes]),[Notes],([tblMaster].[Notes]) Like '*[Forms]![frmSearch]![Notes]*')

Thank again for the help, it's very much appreciated.  I'm open to other methods of doing this, but need those two elements: accounting for null values, and partial text search.
0
 

Author Comment

by:matthewlorin7
ID: 24320350
GRayL:  XXXXX was basically a variable.  In actuality, it reflected whatever field I was searching on ("Name," "Address," "Notes," etc).  What I originally posted works for all my values if there is only one possibility ("John" or "Jim" or "Kevin"), but I need to be able to search for just part of an entry (as would be the case with a key word in the Notes field).  Hope that helps.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:matthewlorin7
ID: 24320540
GRayL:  You answered another very similar question with the below response.  This is very close to what I am looking for.  How to I work this into an IIF statement???

"GRayL:I imagine you are using this in a query somewhere, so the WHERE clause has to be:

WHERE Customer Like "*" & [Forms]![SearchCustomer]![SearchName] & "*";  "

My SQL statement reads:

WHERE ((tblMaster.Notes)=IIf(IsNull([Forms]![frmSearch]![Notes]),[Notes],([tblMaster].[Notes]) Like "*" & [Forms]![frmSearch]![Notes] & "*"))

What is wrong with this?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 24320649
Does this work?

WHERE ((tblMaster.Notes) Like "*" & [Forms]![frmSearch]![Notes] & "*"))

0
 

Author Comment

by:matthewlorin7
ID: 24320800
That worked!!!  Guess I didn't need the IIF.  Thought I would have to account for the null value, but I ran it with a blank in the Notes field and it worked fine.  Thanks!!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24327134
Thanks, glad to help.  Remember with a null value the expession reduces to LIKE "**" which will give you everything.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba DCount with 2 criteria 3 33
combine ShipTo and BillTo Address 3 22
Improving performance of a query that uses a subquery 9 29
Sub Reports 8 21
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now