472,975 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,975 software developers and data experts.

MS Access Question / Multiple Find and Replace Based on Another Table

I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip
Nov 12 '05 #1
2 14802
Not too difficult to do in code, but I would hate to try using macros.

basically you will need to create 2 queries, one based on the Definitions
table, and one on the Products table

Read the first record in the Definitions table for FieldToConvert
' e.g. "Access"
Read the first record in the Products table for FieldToCheck
' e.g. "Outlook Express"
Check if FieldToCheck contains FieldToConvert ' Use the inbuilt
function Instr(FieldToCheck, FieldToConvert)
If Instr(FieldToCheck,he FieldToConvert) = 0 then it can't find the string
' Can't find "Access" in "Outlook"
so check the next record in the products table and so on to the end.
Then read the second record in the Definitions table and repeat the
procedure
If Instr(FieldToCheck,he FieldToConvert) <> 0 then it can find the string '
Can find "outlook" in "Outlook Express"
Read the ReplaceString from the Definitions table
replace the Field to check with the Replace String
update the record
back to check the next record in the Products table.

This is a loose description of what must be done, but if you can get away
from Macros and start using code you will get 1000 times more out of Access

Phil

However the problem is that having run the code to convert "access to "MS
Access",when the program is run a second time, it will again find the word
"Access" so you will end up with "MS MS Access"
"Daniel" <go***********@dmwweb.com> wrote in message
news:69**************************@posting.google.c om...
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 - Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip

Nov 12 '05 #2
Try this on a copy of the tables to check it gives the desired results
UPDATE Table1, Table2 SET Table1.[Product Details] = [Table2].[Replace with]
WHERE (((Table1.[Product Details]) Like "*" & [table2].[Product Name] &
"*"));
Terry

"Daniel" <go***********@dmwweb.com> wrote in message
news:69**************************@posting.google.c om...
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 - Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
5
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/callnetfrcom.asp The Joy of Interoperability Sometimes a revolution in programming forces you to abandon all...
5
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a...
17
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.