Find & Replace (but Replace in a different Column) (2000)

Topic

New Reply

Hello…

Macro[/i] 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

Reply | Quote

Replies

You want to use an update query.

Reply | Quote

To expand on Mark Liquorman’s reply:
– Create a query based on the table.
– Add the column that may contain “GF” and the other column that you want to set to “~”.
– Enter “GF” in the criteria row for the first column.
– Select Query | Update Query.
– Enter “Y” in the Update to row for the first column.
– Enter “~” in the Update to row for the second column.
– Select Query | Run or click the Run button (the exclamation mark) on the toolbar.

Reply | Quote

Guys…

Thanks…but I have no idea how to implement such a thing (at my skill level)…I’m used to creating a Query in Design View…drag/dropping the necessary columns from the Table…& hittin’ the Run button.

How exactly does one create an “Update Query” ?

Thanks,

mark4man

waitaminute…went back & read the post again…the update is selected after the fact? Selected from where?

UPDATE: Read it again…I think I can do this, Hans…your instructions look more clear than I thought. I’m trying this when I get back to work on Monday…it’s a work thing anyway; & I don’t have Access at home. Thanks.

Reply | Quote

You start by creating a query in design view, drag some fields to the query grid, and select Query | Update Query to change the query to an update query. Every step is described in detail in my previous reply!

Reply | Quote

got it…will do (was posting my update while you were replying…sorry).

Reply | Quote

Hans…

The Update Query works like a charm…except for one thing…I have a weird feeling that updating the column that gets the “~”…deletes everything else that exists in that column.

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

Does updating the Comments columns w/ the “~” delete everything else in that column save for that character?

What I’m hoping for is that the update places that character at the end of all the other info…but I got a bad feeling it doesn’t

Thanks,

mark4man

Reply | Quote

As it is now, the “~” will replace whatever was already present in the Comments field. To keep the original contents, enter the following in the Update to line for the Comments column:

[Comments] & "~"

This will append a tilda to the end of the comment.

Reply | Quote

Hans…

That’s not working.

The title of the comments column I was testing was FR DR COMM. So…under Update To: I entered: [FR DR COMM] & “~”. I hit Run…but then a dialog popped up asking me what my parameter value was (???)

I think we’re almost there…all we have to find now is the appropriate entry (in the brackets) to retain the info in that particular column.

Thanks,

MF

Reply | Quote

You must use the exact name of the field, between square brackets. Even the smallest spelling error will cause the parameter prompt.

Reply | Quote

Hans…

That IS the exact name of the feild: FR DR COMM.

Not sure what to do now. I am running a test, first…I created the Query using Design View…I added the appropriate Table…I then dragged the property address info column (simply titled: ADDRESS) & the two test columns, into the Query. My conformity column is entitled: FR DR I A W. My comments column is entitled: FR DR COMM. Not sure where to go from here.

MF

Reply | Quote

Are you sure? It’s spelled field, not feild…

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

No…the entire thing is confidential information.

I’ve done everything you’ve instructed me to do…I don’t know why it’s not working.

MF

UPDATE: How about if I make a copy & delete everything except for Storm Door data (which is my test criteria). Will that work???…it’s much smaller

Reply | Quote

UPDATE:

Well…I just tried it again; & it worked…!!!

In my Update To record I entered: [FR 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

Reply | Quote