Find & Replace (but Replace in a different Column) ??? 1

[BiggyRat]

Hello...

[I need to know if there is a function...perhaps a Macro or Query...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 completely; & 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 in 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 *...something 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 column...(a column 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 column 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,


Biggy Rat

 

[PHV]

Use an update query like this (SQL code):
UPDATE yourTable
SET yourColumn = 'Y', Comments = '~'
WHERE yourColumn = 'GF'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BiggyRat]

PHV...

Thanks...but I have no idea how that would get implemented. I'm used to creating Queries in Design View...drag/dropping whatever Table components I need...assigning my criteria...& hitting the Run button.

What exactly is an "update query"...& how would I plug in a set of instructions (such as you've given me)?

Thanks again,

BR

 

[Lilliabeth]

You can easily create an update query in Design view.

Look in Help for update query.

Be sure to make a copy of the table before you run an update query in case you mess it up.

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[BiggyRat]

OK...

The Update Query worked like a charm...except for one little thing...in the column that got the "~"...everything else that exists in that column was deleted.

I., e., those columns where the "~" will reside (after the update query) is a column labeled Comments. Every database attribute (which are the exterior architectural features for each property we keep track of) has one of these "Comments" columns, which also contains other critical info on the property.

So now...here's where I am: In my conformity column, the GF gets changed to a "Y"...(so far, so good). But then when the Comments column gets updated to my "~", everything else goes by the wayside…all I'm left with is that that character.

What I'm hoping for...is that the update could place that character at the end of all the other info. Any way to implement this?

Thanks,


BR

 

[Golom]

Extending PHV's example

UPDATE yourTable
SET yourColumn = 'Y', Comments = Comments & '~' 
WHERE yourColumn = 'GF'