Partial match for query criteria

Hello,

I am trying to create a query that will deliver results based on matching 
only a part of the text in a table field. For example, if I have a table that 
contains the names, address and phone numbers of 10,000 businesses but I only 
want my query to show me businesses that have the word "National" in their 
name, how would I do that? I'm not sure how to set up the criteria.

Thanks for your help!
0
Utf
1/14/2008 4:32:08 PM
access 16762 articles. 3 followers. Follow

5 Replies
7809 Views

Similar Articles

[PageSpeed] 30

Use the Like operator with wildcards.  In the 'criteria' row of the business 
name column in query design view enter:

    Like "*National*"

Or you can use a parameter:

    Like "*" & [Enter all or part of business name:] & "*"

You'll then be prompted for the name when the query or any form or report 
based on it is opened.

You might get some mismatches if the text you enter is a substring within a 
word for instance, e.g. any names containing "international" would also be 
included in the result set.  To find only distinct words or phrases enter  
the following in the 'field' row of a blank column in the query design grid :

   " " & "First international bank" & " "

and the following in the 'criteria' row

   Like "* " & [Enter all or part of business name:] & " *"

This will find occurrences of the string if preceded and followed by a 
space, but if applied to the field itself would miss words or phrases at the 
start or end of the field of course, which is why you have to apply it to a 
computed column in which spaces are concatenated onto the start and end of 
the value in the field.

Ken Sheridan
Stafford, England

"BJH712" wrote:

> Hello,
> 
> I am trying to create a query that will deliver results based on matching 
> only a part of the text in a table field. For example, if I have a table that 
> contains the names, address and phone numbers of 10,000 businesses but I only 
> want my query to show me businesses that have the word "National" in their 
> name, how would I do that? I'm not sure how to set up the criteria.
> 
> Thanks for your help!

2
Utf
1/14/2008 4:55:01 PM
Create a new query in design view.

Add the [BusinessName] field.

In the selection criterion beneath it, put something like:

    Like * & [Enter partial business name] & *

When you run this query, it will prompt for a partial business name, then 
search for any record with that string somewhere in the [BusinessName] 
field.

Use your real field names.  Add any other fields in the query that you want 
to see.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"BJH712" <BJH712@discussions.microsoft.com> wrote in message 
news:7BE2315C-AF1C-4489-A680-78A2EC7D987B@microsoft.com...
> Hello,
>
> I am trying to create a query that will deliver results based on matching
> only a part of the text in a table field. For example, if I have a table 
> that
> contains the names, address and phone numbers of 10,000 businesses but I 
> only
> want my query to show me businesses that have the word "National" in their
> name, how would I do that? I'm not sure how to set up the criteria.
>
> Thanks for your help! 


0
Jeff
1/14/2008 5:00:08 PM
Thank you! I knew it couldn't be that difficult. You've just saved me so much 
time. 

"Ken Sheridan" wrote:

> Use the Like operator with wildcards.  In the 'criteria' row of the business 
> name column in query design view enter:
> 
>     Like "*National*"
> 
> Or you can use a parameter:
> 
>     Like "*" & [Enter all or part of business name:] & "*"
> 
> You'll then be prompted for the name when the query or any form or report 
> based on it is opened.
> 
> You might get some mismatches if the text you enter is a substring within a 
> word for instance, e.g. any names containing "international" would also be 
> included in the result set.  To find only distinct words or phrases enter  
> the following in the 'field' row of a blank column in the query design grid :
> 
>    " " & "First international bank" & " "
> 
> and the following in the 'criteria' row
> 
>    Like "* " & [Enter all or part of business name:] & " *"
> 
> This will find occurrences of the string if preceded and followed by a 
> space, but if applied to the field itself would miss words or phrases at the 
> start or end of the field of course, which is why you have to apply it to a 
> computed column in which spaces are concatenated onto the start and end of 
> the value in the field.
> 
> Ken Sheridan
> Stafford, England
> 
> "BJH712" wrote:
> 
> > Hello,
> > 
> > I am trying to create a query that will deliver results based on matching 
> > only a part of the text in a table field. For example, if I have a table that 
> > contains the names, address and phone numbers of 10,000 businesses but I only 
> > want my query to show me businesses that have the word "National" in their 
> > name, how would I do that? I'm not sure how to set up the criteria.
> > 
> > Thanks for your help!
> 
0
Utf
1/14/2008 6:21:05 PM
Thank you! That was very helpful and it worked perfectly! 

"Jeff Boyce" wrote:

> Create a new query in design view.
> 
> Add the [BusinessName] field.
> 
> In the selection criterion beneath it, put something like:
> 
>     Like * & [Enter partial business name] & *
> 
> When you run this query, it will prompt for a partial business name, then 
> search for any record with that string somewhere in the [BusinessName] 
> field.
> 
> Use your real field names.  Add any other fields in the query that you want 
> to see.
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "BJH712" <BJH712@discussions.microsoft.com> wrote in message 
> news:7BE2315C-AF1C-4489-A680-78A2EC7D987B@microsoft.com...
> > Hello,
> >
> > I am trying to create a query that will deliver results based on matching
> > only a part of the text in a table field. For example, if I have a table 
> > that
> > contains the names, address and phone numbers of 10,000 businesses but I 
> > only
> > want my query to show me businesses that have the word "National" in their
> > name, how would I do that? I'm not sure how to set up the criteria.
> >
> > Thanks for your help! 
> 
> 
> 
0
Utf
1/14/2008 6:21:14 PM
Thanks,  Just what i was looking for too! :)




-- 
Delboy
0
Delboy
2/4/2008 9:23:14 AM
Reply:

Similar Artilces:

Partial Merge
What is the Partial Merge feature in Windows Server 2008 r2? I believe this is what you are looking for: http://blogs.msdn.com/dstfs/archive/2009/05/04/partial-merges-in-tfs-a-guide.aspx -- Paul Bergson MVP - Directory Services MCTS, MCT, MCSE, MCSA, Security+, BS CSci 2008, 2003, 2000 (Early Achiever), NT4 Microsoft's Thrive IT Pro of the Month - June 2009 http://www.pbbergs.com Please no e-mails, any questions should be posted in the NewsGroup This posting is provided "AS IS" with no warranties, and confers no rights. "Charle" <Charle@l...

How to match and sort
Hi, I was wondering if there was a function or simple program to matc and sort two colums. Let's say the first column is a list compiled b person A and the second is a list compiled by person B. So what I nee done is to match the ones they have in common and filter out the one that are different. For example, let say we're comparing names and birthdays: Birthday (A)Name (A)Name (B) Birthday (B) 6/14 Josh Josephine 3/16 2/15 Chris Sharon 9/4 9/4 Sharon Jerry 8/11 2/28 Lillian Susan 7/12 8/11 Jerry I want it so that when I compare the two lists, it would become: Birthday (A)Name...

field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the wrong place. TblPartNumInfo TblDefectRpt My query for PartNumInfo brings together lots of different tables including customers, contact info, status of jobs. A feature I need to add is a warning if at least one defect report exists for a given part number. It would be shown on the form as a conditional format of the text box where the part number is shown. So I would like to add a field to my query that would return some value (like the part#ID) if at least one Defect Report exists for a part#. I created a sepa...

Help with a Query 02-29-08
How would I do this in Access: I keep getting errors: update target set Assemrec = source.Recno from @Components target Join @Components source on '<$-' + target.RefDes + '>' = source.RefDes On Fri, 29 Feb 2008 12:54:11 -0800 (PST), "the_grove_man@yahoo.com" <the_grove_man@yahoo.com> wrote: >How would I do this in Access: I keep getting errors: > >update target >set Assemrec = source.Recno >from @Components target Join @Components source on '<$-' + >target.RefDes + '>' = source.RefDes This isn...

Return a value if criteria contained in cell
I would like to return a value if criteria is met. If A2 contains the word "apples", then in cell B2, write "apples". If A2 contains the word "oranges, then in cell B2, write "oranges". If A2 does not contain the words "apples" or "oranges", leave the cell blank. Can you help me with a formula for this? Thanks in advance. IF(OR(A2="apples",A2="oranges"),A2,"") "jhicsupt" wrote: > I would like to return a value if criteria is met. > > If A2 contains the word "apples", then in...

Matching transactions
What are the "rules" for money's matching transactions? I find that it constantly mismatches items when the name is different and the amount is different. Is there a way to direct it to "only" match exact amaounts? I don't need it to attempt to match to similar entries. Money 2004 deluxe. Scott ...

MATCH search with text and numbers
I compiled a large database of city blocks in an area. Now I need to be able to pull information from a city block by entering a street address on that block. The database looks something like this: A B C D-E-F 1 From To Street Name Various info. regarding block 2 2000 2099 Webster 3 2100 2199 Webster 4 2200 2099 Webster 5 2000 2099 Clybourn 6 2100 2199 Clybourn 7 2200 2099 Clybourn I need to write up a formula so that when I write: A B 8 2134 Damen The formula searches to determine which range the number...

Partial payment of a bill?
Is it possible to make a partial payment of a bill...which the balance stays as an open bill? Thanks See http://umpmfaq.info/faqdb.php?q=132 for some thoughts on this problem. "redruthann" <redruthann@discussions.microsoft.com> wrote in message news:A0FE488C-5752-413C-B983-1F486D548D03@microsoft.com... > Is it possible to make a partial payment of a bill...which the balance > stays > as an open bill? ...

Need Help with Totals Query
I have a table with daily call records. The table includes fields for: Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed; Locale; Length of Call. I created a query that will show calls lasting longer than 3 minutes for a given time period, for example calls between 10 AM and 12 PM. The query prompts the user for date(s). Now I need to add counts for: total calls; out-bound calls; and in-bound calls by user. I tried including a totals row in the query and adding a colum to count outbound calls, but I get the error that "This expression is typ...

SUMIF & Partial Strings (followup posting)
I couldn't post a follow up, so I've created a new message here... From: Jerry W. Lewis (post_a_reply@no_e-mail.com) Subject: Re: SUMIF & Partial Strings Date: 2003-01-27 02:14:06 PST Use... =SUMIF(A3:A29, "=*Alls*", B3:B29) or =SUMIF(A3:A29, "=*" & A52 & "*", B3:B29) where A52 equals "Alls" This way, if you have two entries that begin "Alls", they will be added together. Drew. --------------------------------------------------------------- From: Mike Copeland (mrcNOSPAM2323@cox.net) Date: 2003-01-27 20:58:31 PST ...

query field limit?
Hi, I was creating a query in design view (access 07), and because it was a bigger query than I normally do, and noticed that the number of columns stopped at 16. Is the number of fields in a query really limited to 16? If so, I find that very hard to believe. If not, why does it stop at 16 and how do I add additional columns? Thank you 255 fields is the max. You can get the limitations in the Help, under "Access Specifications". Hoping it may help, Vanderghast, Access MVP "zxcv" <zxcv@discussions.microsoft.com> wrote in message news:6BEA6D7D-6A74-4CE5-8C...

One partial table, one master table
I've got a listing of 3000 personnel. Personnel in column A, supervisors in column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? You can use VLOOKUP to find the supervisors for sheet 1 from sheet 2 In the column next to the personell in the short list try this formula =VLOOKUP(A1,Sheet...

Filtering by three criteria including top ten value
This is hopefully a littler better explanation of an earlier post of mine. I need to filter a list of information by first the Year and Product. I then need to know of those now filtered results what are the top ten results by revenue a third column. Have tryed Large functions and it didn't work. Any help would be greatly appreciated. thanks, Also I have attached my spreadsheet as an example There was no attachment. the code below uses autofilter to filter the 3 columns. then the code uses specialcells to select the visible rows to get the results. I basically recorded...

Formula in MS Query
I am trying to import external data into Excel spreadsheet. One of the tables in the external data has a field "Time" for Date and time of transaction. In MS query I inserted a new column with a new name and inserted a formula Left(Filename.Time,11). The result came as "Dec 2,2003", whereas I wanted this to show as "12/02/2003", in the true date format.. What function I should use to get the desired result from MS Query. Thank you all for the help. Krish You could import the Time field in its Date/Time format, then reformat the column in Excel (Format>...

Xtab query question.
I am trying to put a criteria into a xtab query via the Combo on a form. I use the builder to get "Forms![FrmPopupCustProd]![Combo]0" and this is inserted into the criteria field of the xtab query. But the query opens to give no results at all. Is this the wrong treatment for a crosstab query? Please help, Frank A few suggestions, Frank: 1. Is the zero at the end really part of the parameter? (Perhaps it should read Combo0 inside the square brackets?) 2. Make sure the field you apply this criteria under reads Where in the Total row. If you need to show the output of t...

filter subform created from query
Hi, How can i filter a subform created from a query with an unbound combo box? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1 Put a WHERE clause in your subform's record source query to filter on the value of the Combo Box: WHERE [SomeField] = Forms!MyFormName!MyComboName Then in the After Updat event of the Combo: Me.MySubformControl.Form.Requery -- Dave Hargis, Microsoft Access MVP "spiz via AccessMonster.com" wrote: > Hi, > How can i filter a subform created from a query with an unbound co...

INDEX MATCH LARGE Ranking
I'm using data in one sheet to create rankings in another sheet. However, if there are duplicate values, it only returns the first matched value. For example...let's say im a teacher and am keeping track of my student's test grades. One sheet has the raw data (name and grade). The other sheet is ranking these students based on their grade, using the LARGE function, and matching these grades to students using INDEX MATCH. Now let's say two students got a 90 (A1=John and B1=90, A5=Joe and B5=90). It's returning John's name twice because he is at the top of the list....

Match words
I want to fill a cell with color if it contains a word that contai letters that I specify. For example words then end with letter "s". Wildcard search doesn't seem to work? What is the formula to use -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 use conditional formatting and formula is =RIGHT(A1)="s" click the format button and pattern and select the colour -- Reg...

balances don't match
Hi anyone have an idea how to resolve this: I have an investment account set up with an associated cash account...which is baqsically used as a checking account. It is setup with online update via Schwab. The actual balance of the account matches the balance on the "cash transactions" page of the account register. But my "net worth" report and the home page show the balance as significantly lower (thousands of dollars) and when my balance is low, it even shows negative... all the while the register still shows the accurate balance. any clues ? In microsoft.public....

Partial Credits
Hello! How do you credit invoices from past quarters? -- Jen Garrison Bancsource, Inc. Senior Analyst 417-732-7238 ext. 1259 ...

Query Tied to Form doesn't work
I have a button on a form that populates a text box on the same form with text. I have a query where a field's criteria is a reference to the text box on the form so the text in the text box becomes the criteria for the query. After I click the button to populate the text box on the form and then run the query, it comes up blank (should be records). If I click the button on the form and copy the text from the text box to the query (in stead of a reference to the text box on the form) the query works fine. Any ideas why the query won't work with the reference to the text box on...

Conditional formatting does not work with MATCH / VLOOKUP
I do have two sheets and use conditional formatting. One sheet works fine. When I get a value on that sheet the other sheet with MATCH and VLOOKUP, and use the same conditional formatting on that sheet, CF does not work at all. Bart Excel 2003 I have three conditions, in the mode "Cell Value". When the value match the 3rd condition, it is indicated according tio that condition. All other value who should match the 1st and 2nd conditions are not indicated to their respective conditions. Bart On Dec 26, 6:11 pm, "AA Arens" <bartvandon...@gmail.com> wrote: > I...

Hlookup and match or index usage -- incrementing to next cell
I have a formula that works great, I just can't figure out how to get it to move dynamically to the next cell =HLOOKUP(startmo,PipelineNA,15) so it looks for the start month of october 2008, in the PipelineNA data range, adn then goes down 15 rows and displays the value. However, I need to take this formula and copy it out for 12 months.... So it needs to show the November 2008 data in row 15....How do I get it to increment dynamically (without having to setup/hard code a range for every month value)? Thoughts? Thanks! Remove the "startmo" named range and replace it with the...

Fight spam with LDAP querying (Exchange 2000)
Hi everyone, I am trying to design a new 2-tier anti-spam email system with a 3rd party product doing spam filtering in front of an E2k box. I still expect to have 30-40% traffic originating from mispeled NDRs. Is there a way I can make the Exchange box (or any other box in front of it) perform LDAP queries and only forward emails to be scanned if they are legitimate AD users. Many thanks, Kou ...

query database
Every day I have to copy a database (a client listing that is added to daily by many other people) into excel and then find the new clients. This information is copied from a business programme which I am able to paste into excel. The software used to record the original data does produce a unique code for each client which, I think, would be the way to find the new listings. I have to produce a weekly report showing the new clients added during the week (on a daily basis). Is there a formula that will compare the list (todays list against yesterdays list) to show me the new clients rather...