A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"any part of field" query



 
 
Thread Tools Display Modes
  #1  
Old October 3rd, 2004, 06:43 PM
Roger in Diego
external usenet poster
 
Posts: n/a
Default "any part of field" query

Novice needs help. I am wondering if you can design a query that will 'hit'
on a partial field match like the "Find" feature does. Thanks
  #2  
Old October 3rd, 2004, 08:41 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Roger

In the criterion row under the field you are searching, enter something like
(actual syntax may vary):

Like *[Enter something you want to find]*

This finds whatever is entered in ANY location.

If you use:

Like [Enter the starting few characters]*

Access will only search for values STARTING with what's entered.

--
Good luck

Jeff Boyce
Access MVP

  #3  
Old October 3rd, 2004, 08:49 PM
Dan Artuso
external usenet poster
 
Posts: n/a
Default

Hi,
You can try using Like and the wildcard *

Your criteria could be something like:

Like '*emp*'

which will find all matches where that field contains "emp"

--
HTH
Dan Artuso, Access MVP


"Roger in Diego" Roger in wrote in message
...
Novice needs help. I am wondering if you can design a query that will 'hit'
on a partial field match like the "Find" feature does. Thanks



  #4  
Old October 4th, 2004, 01:11 AM
faxylady
external usenet poster
 
Posts: n/a
Default

I have an interest in this particular topic also. M question is how to find
items that are missing a comma at the end when all other items in the field
have commas at the end. What I am doing is composing a table of email
addresses in a table. It has only one field. I just got the "Automatically
Delete duplicate records from a table" help file to work properly--installing
Office SP3 was a big help. Now that most of the duplicates are gone, I have
a few left. These are the ones without the required commas at the end.
Visual duplicates are created because the correct entries with the commas at
the end are there also. When I try to manually delete these records I get an
error message saying that if I attempt to change the record, I would create
duplicates. Step 7 from the help file creates an index key.

I am including my previous requests for yyour information. The answers I
received were very helpful. I also found part of my problem to be solved
after installing Office SP3.

Automatically Deleting Duplicated Data from a Table 040104


A project that I am working on requires deletion of duplicated data from a
table. The table consists of only 1 field named Email Addresses. My plans
are to use the email addresses for sending email. I have used the text data
type instead of the hyperlink data type because I wish to perform sorting
operations alphabetically on them periodically as I continue to enter the
email addresses. So far, I have collected over 800 email addresses from
various sources. The hyperlink data type inhibited me from using the sort
feature.

While collecting the data from various sources, many duplicates are
produced. Therefore, I turned to the help files and located the topic,
Automatically Delete Duplicate Records from a Table. Every effort has been
made to follow the instructions meticulously.

Steps 1 –5 are fairly simple, creating a new table by copying and pasting in
the Database window.. Step 6 states to “Open the new table in Design view,
and select the field(s) that contained duplicates in the table you copied.”
No problem here.

Step 7, which states, “Click Primary Key on the toolbar to create a primary
key based on the selected fields,” proceeds okay.

The difficulty begins with Step 8, which says to “Save and close the table.”
When “Yes” is clicked, an error message appears stating, “The changes you
requested to the table were not successful because they would create
duplicate values in the Index, Primary Key, or relationships. Change the
data in the field or fields that contain duplicate data, remove the Index, or
redefine the index to permit duplicate entries and try again.”

The second part of this help file is To append only unique records to the
new table.

Step 1, Create a new query based on the Original table containing duplicates.

Step 2, In query Design view, click the Query Type on the toolbar, and then
click Append Query.

Step 3, In the Append dialog box, click the name of the new table from the
Table Name list, and then click OK.

Step 4, Include all the fields from the original table by dragging the
asterisk (*) to the query design grid.

Step 5, Click Run on the toolbar.

Step 6, Click Yes when you receive the message that you’re about to append
rows.

Trouble begins here with Step 7, Click Yes when you receive the message that
Microsoft Access can’t append all the records in the append query. This
transfers only unique records to your new table and discards the duplicates.
This step does not appear. No error message appears.

Steps 8 and 9 to see the results cannot be performed.

The Office 2000 version of Access is being used on a Pentium III, 500 Mg
machine with a 120gg hard drive. Windows 98 is the operating system.


Please help me to delete the duplicated records from my table.

Thank you.


"Jeff Boyce" wrote:

Roger

In the criterion row under the field you are searching, enter something like
(actual syntax may vary):

Like *[Enter something you want to find]*

This finds whatever is entered in ANY location.

If you use:

Like [Enter the starting few characters]*

Access will only search for values STARTING with what's entered.

--
Good luck

Jeff Boyce
Access MVP


  #5  
Old October 4th, 2004, 01:44 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Sun, 3 Oct 2004 17:11:07 -0700, "faxylady"
wrote:

I have an interest in this particular topic also. M question is how to find
items that are missing a comma at the end when all other items in the field
have commas at the end.


A criterion of

NOT LIKE "*[,]"

should do the trick.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Print Taher Setting Up & Running Reports 1 August 31st, 2004 09:07 PM
Autonumber Ally H. General Discussion 7 August 27th, 2004 04:51 PM
criteria in query part of field text only MemphiDoug Running & Setting Up Queries 1 May 30th, 2004 06:02 PM
field manipulation Steve Running & Setting Up Queries 2 May 28th, 2004 03:12 PM


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.