Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    You want to use an update query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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!

  5. #5
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  6. #6
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    got it...will do (was posting my update while you were replying...sorry).
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  7. #7
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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:
    <code>
    [Comments] & "~"
    </code>
    This will append a tilda to the end of the comment.

  9. #9
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Are you sure? It's spelled field, not feild... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  13. #13
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

  14. #14
    4 Star Lounger
    Join Date
    Dec 2001
    Location
    Bordentown, New Jersey, USA
    Posts
    405
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ADK Pro Audio Quad Pro, w/ Intel Core i7 2600 CPU | 8 GB DDR3-1600 RAM | Seagate 500 GB SATA II (Primary) HD | Seagate 1 TB SATA II (Audio Data) HD | Win 7 Pro 64-Bit | Lynx Aurora8 ~ AES16 | Universal Audio UAD-1 | Cakewalk SONAR PE XI | Steinberg WaveLab 7 | NI Komplete 5 | Band-In-A-Box 2012

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •