Find and Replace Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I specify field values in many queries to filter records. Ex: Not like
"Company X" and Not Like "Company Y" The field values that I have used to
filter the data have stayed the same for years but recently changed so I must
change the values in all those query criterion. I can easily change the
data values with an update query but how do I change the criteria values
without opening every query in design view to search for and replace the
outdated criteria?

Thanks for any help
 
There are several Search and Replace applications on the market. I use
Speed Ferret, but there are a couple of others as well, and I think one or
more of them are free. Google on "Access +search +replace +tool.

The current version of Speed Ferret (4.?) primarily works with Access 97,
2000, XP, but will also work with 2003 if you have one of the older versions
of Access as well. It is a great tool, and will easily pay for itself the
first time you use it. It will search all of the objects (tables, queries,
forms, macros, ...) and properties in your database for the search string,
will display the source and what it will look like if the change is
implemented, and gives you the option of selecting which of the occurances
you want to actually update. The only thing it won't change is data in your
tables.

HTH
Dale
 
I specify field values in many queries to filter records. Ex: Not like
"Company X" and Not Like "Company Y" The field values that I have used to
filter the data have stayed the same for years but recently changed so I must
change the values in all those query criterion. I can easily change the
data values with an update query but how do I change the criteria values
without opening every query in design view to search for and replace the
outdated criteria?

Thanks for any help
Click to expand...

I think you have bite one of several potential bullets.

A) Open every query design and search & replace the items
B) Get crafty with VBA and loop through the query objects collection
and search / replace stuff in the SQL property
C) Build a table of exclusions (all excluded companies in one column)
and, Open every query and modify it to include a join to the
exclusions table with some sort of "not exists" constraint

Option A) is easiest, but tedious if you have many queries, and will
have to be repeated every time the exclusions change
Option B) is trickier but only has to be done once (except for very
minor code change if the exclusions change again in the future)
Option C) is also tricky but 1) will accommodate 0->infinity items in
the exclusion list and 2) is easiest to maintain if the exclusions
change change in the future

If you have queries embedded in forms, all the above may need to be
modified.

HTH
 
Products to document the database and do a global find and replace. All
have Access 97 and later versions. Some don't have an Access 2003 version,
but may still work with Access 2003.

Shareware (Try and Buy):
Find and Replace http://www.rickworld.com

Commercial (Try and Buy)
Speed Ferret http://www.moshannon.com

Commercial
Total Access Analyzer http://www.fmsinc.com

Free (For Access XP Only??):
http://www3.bc.sympatico.ca/starthere/findandreplace

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top