Find & Replace (but Replace in a different Column)

[mark4man]

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,


mark4man
Access 2000

 

[odin1701]

You can do it with an update query.

The SQL would look something like this:

UPDATE tblInfo SET tblInfo.IAW = "Y", tblInfo.Comments = "Y~"
WHERE (((tblInfo.IAW)="GF"));

 

[mark4man]

Thanks...will give it a whirl when I get back to work on Monday...it's a work thing anyway; & I don't have Access at home.

MF


BTW - What the h_ll is an SQL, anyway ???

 

[odin1701]

SQL = Structured Query Language

Basically when you design a query - you're running a SQL statement on your data when you run the query.

More basic info here:

http://www.w3schools.com/sql/default.asp

 

[mark4man]

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…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,


mark4man

 

[odin1701]

hmm....I didn't know you wanted to add it on to existing text. In that case:

UPDATE tblInfo SET tblInfo.IAW = "Y", tblInfo.Comments = tblInfo.Comments & "~"
WHERE (((tblInfo.IAW)="GF"));

 

[mark4man]

odin1701...

Let's back up a bit:

For my test...the conformity column is titled: FR STM DR I A W. The comments column is entitled: FR STM DR COMM. So I revised your code to:

UPDATE tblInfo SET tblInfo.FR STM DR I A W = "Y", tblInfo.FR STM DR COMM = tblInfo.FR STM DR COMM & "~"
WHERE (((tblInfo.FR STM DR I A W)="GF"));

Trouble is...I have no idea where to plug that in at.

I created the Query using Design View...I added the appropriate Table...I then dragged the property address info column (simply titled: ADDRESS) & the above two columns, into the Query...I then defined my FR STM DR I A W criteria as "GF"...& then went to the command bar at the top of the page. I selected Query...but then the only drop down that seemed appropriate for your code was SQL Specific. The choices are Union, Pass-Through & Data Definition...but I don't know where to go from here.

Thanks,

MF

 

[odin1701]

Okay - if your tables have spaces, you'll need to do this:

UPDATE tblInfo SET [tblInfo].[FR STM DR I A W] = "Y", [tblInfo].[FR STM DR COMM] = [tblInfo].[FR STM DR COMM] & "~"
WHERE ((([tblInfo].[FR STM DR I A W])="GF"));

Just go to queries and choose create a query in design view. When the choose table dialog comes up, just hit cancel.

You should have a blank query. Right click on the title bar of the query and you should see several options (datasheet view, etc.) You want to pick SQL View. That will give you a place you can type and it will probably say SELECT already. Just delete everything and paste the SQL code above in there. After pasting, you can switch back to design view by right clicking the title bar again and it should show your tables and fields and criteria, etc.

Once you see how an update query is setup in the design view - it should be easier to grasp how to do them in the future. You really don't need SQL as this is a very simple update query.

 

[mark4man]

no...that doesn't work...it now asks me for Parameter Values for [tblInfo].

Please...this should be simple...why isn't it working?

 

[odin1701]

hmm...maybe just try tblInfo.[FR STM DR I A W] - take the []'s off of all the tblInfo.

That really shouldn't matter at all though. Something isn't right - something is spelled wrong or something. I know that code will work as long as your table is called tblInfo and those are the correct field names...it will work.

What is the data type of each of the fields that you are updating?

 

[mark4man]

Well...

That's the answer...the table is entitled: TH Inspection File.

But...I've already established what the Table is...when I first create the Query...I don't get it. I should just be required to give that column the update criteria. (???) I.e., if you look at the Query form, the 2nd record IS the Table.

MF

 

[odin1701]

If your table you are making these changes in is named that, then you will need this:

UPDATE [TH Inspection File] SET [TH Inspection File].[FR STM DR I A W] = "Y", [TH Inspection File].[FR STM DR COMM] = [TH Inspection File].[FR STM DR COMM] & "~"
WHERE ((([TH Inspection File].[FR STM DR I A W])="GF"));

 

[mark4man]

OK...

I'm not using the code entries...I'm using a simple Update Query.

But...I just tried it again; & it worked...!!!

In my Update To record I entered: [FR STM DR COMM] & "~"...& it worked perfectly...it retained all previously entered data to that column & added an ~ to the end of the line.

[Have no idea why it did not work previously.]

Thanks very much,

mark4man

 

[odin1701]

You might have just made a typo that first time. I knew it would work

If you look at the SQL in the working query you made, maybe that would help you see what it's doing

 

[mark4man]

funny you should mention that...

...'cause I can't seem to get back into the Query, itself.

When I try...I get the big message that reads:

You are about to run an update query that will modify data in your table...are u sure y'alls really wants to do dis here?

I hit Yes, another message pops up that tells me I am

about to update 0 (zero) rows (since, you've obviously run the doggone query once already, you moron)

...but I hit Yes...the Query runs...& that's it.

It doesn't open.

So...without sounding more idiotic than I have already...how do I actually get back into the Query itself?...to have a look at that code, as you suggested (& that I want to see myself, for the educational aspect thereof).

Thanks,

mark4man