Find & Replace (But replace in a different Column)

[Mark Forman / DRC, TRCT]

Hello...

[I need to know if there is a function...perhaps a Macro, or a more complex
'Find & Replace'...that can accomplish the following...'cause my database is
huge; & I'm hoping not to do this manually]:

I keep an Access database for my homeowners' association on the condition &
conformity of 24 different exterior attributes for about 2400 townhomes.
The Board of Directors wants to move away from using the term
"Grandfathered", which is distinguished by the acronym "GF" in the database
column that shows conformity (labeled IAW). We use this database to
generate annual architectural reports for our 10,000 residents.

Our board wants the entry "GF" replaced with a "Y"...they want the GF entry
removed from the report; & they're serious about it.

Now...I have already created a Query that has located all the GF entries in
their various columns of various records...which our Board wants me to use
an internal "in-office" database (in order to keep track of what attributes
actually were grandfathered originally...as a reference for our field
inspector when he inspects the townhomes).

But we also want to create an unassuming symbol on the report...perhaps an ~
or a *...that will alert the inspector to an attribute that once was
grandfathered, on his field report. This is to be done so that he will not
be flying blind when he comes across a non-conforming attribute...i.e., he
will see the symbol & know that that symbol means there used to be a "GF" in
it's place.

BUT...we want to move that symbol to a different record...(a record labeled
"Comments").

I could run the 'Find & Replace' function...find all the GF's...& replace
them with a Y~...but that ~ symbol would go right back in the very same
record that the GF came out of.

So...is there a way to find all the GF's in one column; & replace them w/ a
Y in that same column...but have a symbol "~" appear in another column of
the same record?

I know it sounds complicated...but can it be done?

Thanks,


mark4man

 

[KARL DEWEY]

Backup the database first. Then make a second backup on some portable media.
Use an update query like this ---
UPDATE Mark SET Mark.[one column] = Replace([one column],"GF","Y~"),
Mark.[another column] = "Y~ " & [another column]
WHERE (((Mark.[one column]) Like "*GF*"));

Substitue your table name for Mark and the field names for 'one column' and
'another column'. This query concatenates the Y~ with space to the start of
the contents of 'another column'.
----- WORD OF CAUTION ----conformity of 24 different exterior attributes for about 2400 townhomes.
If these exterior attributes are stored in different field (like a
spreadsheet0 and not different records this will not work.
--
KARL DEWEY
Build a little - Test a little

Hello...

[I need to know if there is a function...perhaps a Macro, or a more complex
'Find & Replace'...that can accomplish the following...'cause my database is
huge; & I'm hoping not to do this manually]:

I keep an Access database for my homeowners' association on the condition &
conformity of 24 different exterior attributes for about 2400 townhomes.
The Board of Directors wants to move away from using the term
"Grandfathered", which is distinguished by the acronym "GF" in the database
column that shows conformity (labeled IAW). We use this database to
generate annual architectural reports for our 10,000 residents.

Our board wants the entry "GF" replaced with a "Y"...they want the GF entry
removed from the report; & they're serious about it.

Now...I have already created a Query that has located all the GF entries in
their various columns of various records...which our Board wants me to use
an internal "in-office" database (in order to keep track of what attributes
actually were grandfathered originally...as a reference for our field
inspector when he inspects the townhomes).

But we also want to create an unassuming symbol on the report...perhaps an ~
or a *...that will alert the inspector to an attribute that once was
grandfathered, on his field report. This is to be done so that he will not
be flying blind when he comes across a non-conforming attribute...i.e., he
will see the symbol & know that that symbol means there used to be a "GF" in
it's place.

BUT...we want to move that symbol to a different record...(a record labeled
"Comments").

I could run the 'Find & Replace' function...find all the GF's...& replace
them with a Y~...but that ~ symbol would go right back in the very same
record that the GF came out of.

So...is there a way to find all the GF's in one column; & replace them w/ a
Y in that same column...but have a symbol "~" appear in another column of
the same record?

I know it sounds complicated...but can it be done?

Thanks,


mark4man

 

[Mark Forman / DRC, TRCT]

Karl...

Thanks, man...but...holy cow...where do I start?

What I mean by that is...I normally set my queries up in Design View; & then
simply drag & drop the components I need. I've never seen any kind of query
functionality associated with "instructions", such as you've given me.

Where do I plug those in?

Thanks.


mark4man

Backup the database first. Then make a second backup on some portable
media.
Use an update query like this ---
UPDATE Mark SET Mark.[one column] = Replace([one column],"GF","Y~"),
Mark.[another column] = "Y~ " & [another column]
WHERE (((Mark.[one column]) Like "*GF*"));

Substitue your table name for Mark and the field names for 'one column'
and
'another column'. This query concatenates the Y~ with space to the start
of
the contents of 'another column'.
----- WORD OF CAUTION ----conformity of 24 different exterior attributes for about 2400 townhomes.
If these exterior attributes are stored in different field (like a
spreadsheet0 and not different records this will not work.
--
KARL DEWEY
Build a little - Test a little

Hello...

[I need to know if there is a function...perhaps a Macro, or a more
complex
'Find & Replace'...that can accomplish the following...'cause my database
is
huge; & I'm hoping not to do this manually]:

I keep an Access database for my homeowners' association on the condition
&
conformity of 24 different exterior attributes for about 2400 townhomes.
The Board of Directors wants to move away from using the term
"Grandfathered", which is distinguished by the acronym "GF" in the
database
column that shows conformity (labeled IAW). We use this database to
generate annual architectural reports for our 10,000 residents.

Our board wants the entry "GF" replaced with a "Y"...they want the GF
entry
removed from the report; & they're serious about it.

Now...I have already created a Query that has located all the GF entries
in
their various columns of various records...which our Board wants me to
use
an internal "in-office" database (in order to keep track of what
attributes
actually were grandfathered originally...as a reference for our field
inspector when he inspects the townhomes).

But we also want to create an unassuming symbol on the report...perhaps
an ~
or a *...that will alert the inspector to an attribute that once was
grandfathered, on his field report. This is to be done so that he will
not
be flying blind when he comes across a non-conforming attribute...i.e.,
he
will see the symbol & know that that symbol means there used to be a "GF"
in
it's place.

BUT...we want to move that symbol to a different record...(a record
labeled
"Comments").

I could run the 'Find & Replace' function...find all the GF's...& replace
them with a Y~...but that ~ symbol would go right back in the very same
record that the GF came out of.

So...is there a way to find all the GF's in one column; & replace them w/
a
Y in that same column...but have a symbol "~" appear in another column of
the same record?

I know it sounds complicated...but can it be done?

Thanks,


mark4man

 

[KARL DEWEY]

Do the backups!

Start making the query as you normally would create a query by opening in
Design View. Close the popup where you would normally select tables and
queries.
Click on menu VIEW - SQL View. Paste the SQL statement below in the window
that opens.

UPDATE Mark SET Mark.[one column] = Replace([one column],"GF","Y~"),
Mark.[another column] = "Y~ " & [another column]
WHERE (((Mark.[one column]) Like "*GF*"));

Substitue your table name for Mark and the field names for 'one column' and
'another column'.

Remove any added hard returns the posting and pasting process may place in
the SQL. There should only be one return before the word WHERE.

As I said if your table structure is laid out like a spreadsheet this will
not work. Your phrasing 'GF entries in their various columns' leads me to
believe you have a spreadsheet format.

If you need further explaination then copy the query you said you had
created that has located all the GF entries in their various columns of
various records...
--
KARL DEWEY
Build a little - Test a little

Karl...

Thanks, man...but...holy cow...where do I start?

What I mean by that is...I normally set my queries up in Design View; & then
simply drag & drop the components I need. I've never seen any kind of query
functionality associated with "instructions", such as you've given me.

Where do I plug those in?

Thanks.


mark4man

Backup the database first. Then make a second backup on some portable
media.
Use an update query like this ---
UPDATE Mark SET Mark.[one column] = Replace([one column],"GF","Y~"),
Mark.[another column] = "Y~ " & [another column]
WHERE (((Mark.[one column]) Like "*GF*"));

Substitue your table name for Mark and the field names for 'one column'
and
'another column'. This query concatenates the Y~ with space to the start
of
the contents of 'another column'.
----- WORD OF CAUTION ----

keep an Access database for my homeowners' association on the condition
&

conformity of 24 different exterior attributes for about 2400 townhomes.
If these exterior attributes are stored in different field (like a
spreadsheet0 and not different records this will not work.
--
KARL DEWEY
Build a little - Test a little

Hello...

[I need to know if there is a function...perhaps a Macro, or a more
complex
'Find & Replace'...that can accomplish the following...'cause my database
is
huge; & I'm hoping not to do this manually]:

I keep an Access database for my homeowners' association on the condition
&
conformity of 24 different exterior attributes for about 2400 townhomes.
The Board of Directors wants to move away from using the term
"Grandfathered", which is distinguished by the acronym "GF" in the
database
column that shows conformity (labeled IAW). We use this database to
generate annual architectural reports for our 10,000 residents.

Our board wants the entry "GF" replaced with a "Y"...they want the GF
entry
removed from the report; & they're serious about it.

Now...I have already created a Query that has located all the GF entries
in
their various columns of various records...which our Board wants me to
use
an internal "in-office" database (in order to keep track of what
attributes
actually were grandfathered originally...as a reference for our field
inspector when he inspects the townhomes).

But we also want to create an unassuming symbol on the report...perhaps
an ~
or a *...that will alert the inspector to an attribute that once was
grandfathered, on his field report. This is to be done so that he will
not
be flying blind when he comes across a non-conforming attribute...i.e.,
he
will see the symbol & know that that symbol means there used to be a "GF"
in
it's place.

BUT...we want to move that symbol to a different record...(a record
labeled
"Comments").

I could run the 'Find & Replace' function...find all the GF's...& replace
them with a Y~...but that ~ symbol would go right back in the very same
record that the GF came out of.

So...is there a way to find all the GF's in one column; & replace them w/
a
Y in that same column...but have a symbol "~" appear in another column of
the same record?

I know it sounds complicated...but can it be done?

Thanks,


mark4man