Search errors in Access 2000

[Steve Soldo]

Hello. One of the memo fields in a Access 2000 database I use for my
business is NOT amenable to search. Either manually or by macro, searching
for text that is clearly present in the field returns the message "Microsoft
Access finished searching the records. The search item was not found".

Occasionally will work after "compact and repair". I suspect some form of
corruption but I have no idea how to fix this, short of rewriting the whole
database which I would rather not do.

Any ideas?

 

[Tom Wickerath]

Hi Steve,

When I have seen memo field corruption in the past, I have also noticed
strange symbols in the displayed data. Are you seeing any evidence of this?

Can you provide an example of a memo field data, and the SQL statement for a
query that fails to locate this data?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Steve Soldo]

Mr Wickerath,

Thank you so much for your reply. No there are no strange characters in the
memo field. I don't really use a "SQL" statement for the query.

Let me clarify: Let's say I type "Mary had a little lamb" in the memo
field. If I positon the cursor before the word Mary, the go to Edit-->Find
and type in "little" (without quotes) and then click "Find Next", the
response is "Microsoft Access has finished searching the records. The search
item was not found."

It is like the program cannot "see" any of the contents of the field that
are obviously there.

Any suggestions would be immensely appreciated. Happy New Year--


Steve Soldo
Pasadena, CA

 

[Tom Wickerath]

Hi Steve,

Are you setting the Match to "Any part of field"? This is required to find
the word "little" in your example. Note that the default search is Whole
field.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Steve Soldo]

I usually use "Start of the field" when search using a micro. However, I get
the same error with "Any part of field", "whole field" and "Start of field".
Does not make a difference.

Frustrating...

 

[Tom Wickerath]

Hi Steve,

I'm feeling your pain now when using the Edit | Find dialog method of
searching. I just opened a clean copy of Northwind in Access 2003, and then
opened the Employees form. I selected the Personal Info tab, and clicked into
the Notes field, at the beginning of the sentence. This is for employee
"Nancy Davolio", and the mouse cursor is positioned just before the word
"Education". I did a search for this word, and got the same result that you
did. Interestingly, I was able to find this record if I clicked anywhere else
within the Notes field for this record, or started my search using a
different employee record.

I have not used the built in search or Filter by Selection/Filter by Form
features for many years. In applications that I create, I provide an unbound
QBF (Query By Form) search form, and I remove the standard menu, so that one
cannot invoke the Edit | Search or Filter capabilities. I know this may not
be the answer you want to read, but I think you will be much happier with the
search capabilities in the long run, if you move to this type of search.
Implementing a fully capable QBF search form requires some experience writing
queries, as well as writing and debugging VBA code. Here are some examples,
which range from very simple to somewhat more complex:

Easiest--very little VBA code. Includes Word doc. written by Michael
Hernandez:
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

Fairly easy--demonstrates "iterating" the .ItemsSelected property of a
multi-select list box:
http://www.accessmvp.com/TWickerath/downloads/elements.zip

A bit more complex
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008


Access MVP Allen Browne has a more generic sample available, that you might
want to look into as well:

Search criteria
http://allenbrowne.com/ser-62.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Steve Soldo]

I'm not sure I am smart enough to do what you suggest.

"Easy" fix for corrupted memo field: Copy the database. Delete the problem
field from the form in design view, and then delete the field from the table.
Then compact and repair the database, and close it.

Reopen the database and add the same Field name to the table. Go back to
design view of the form and reinsert the field in the desired place. Go to
the copy of the databse, open the table and copy the data, and then paste in
the "new" field.

So far works fine. Primitive I know. Does this seem like a tenable solution?

 

[Tom Wickerath]

Hi Steve,

I'm not convinced that the problems with the Edit | Find search capability
involve corruption of a memo data type field. The problem I outlined last
night were observed using a "fresh" (ie. unaltered) copy of the Northwind.mdb
sample database that shipped with Access 2003.

I suppose if the steps you outlined work for you, then great. I really doubt
that it is necessary to delete the text box from the form, and later re-add
it. In the past, I have recovered data in corrupted memo fields by exporting
the data in the table to a flat text file, and then appending the data back
into an empty copy of the table. See the topic "Recovering from table
corruption", as shown on page 3 of a Word document I call Access Links.doc.
You are welcome to download a zipped copy from here:

http://www.accessmvp.com/TWickerath/downloads/accesslinks.zip

However, that said, what leads you to believe that there is any corruption
present?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________