Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2006
    Posts
    18

    Red face Unanswered: Find Replace Text in Table in several fields

    I have been using Access for years, but macros only...VERY little module VB, so I understand the "programming" only slightly.

    I'm importing an Excel table of several columns which all contain numbers which mostly are decimals
    .247 .282 .273 .244
    .186 .199 .201 .180
    .743 .722 .712 .739
    etc
    But sometimes, they have a "<" or a "P" or an "F" behind them:
    .247 .282P .273 .244P
    .186 .199 .201< .180<
    .743 .722F .712 .739
    etc
    I want to strip out all records with the "F"s and keep them in a separate table, and I know I can do this by importing to a table with numberical values and save the "F"s in an error file.

    BUT I want to keep the records in the original file with the "<"s and "P"s and just change the "<" and the "P" to "" (nothing, erase them)

    I find that SendKeys is a monster not to be trusted. I can't find any other way to do this in a macro, and I don't know enough to do it in code.

    Thank you for your help.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Create a query (make table or append) and set the criteria to *F* in different rows under each of the appropriate fileds.

    Create a delete query with the same criteria.

    Run the first query then run the second query.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Or simply do a find and replace on that column in the table.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2006
    Posts
    18

    Thank you

    Thanks, but I want to automate this, and Find/Replace doesn't work with the SendKeys (or at least I can't make it work). Also, Finding just anything with an *F* doesn't help me strip out the "P's" and "<'s".

    I think I have a partial fix.....
    New Yes/No column for "F's"...
    Update Query to -1 for anything with "F's".

    But I still need to replace those pesky <'s and P's before converting data to numbers.

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Are the unwanted characters always in the same place in the field (beginning, end, ect)?

  6. #6
    Join Date
    Oct 2006
    Posts
    18
    Yes....

    976.275--------.266---------4.737--------336.654
    922.166--------.277<--------4.632<-------331.144
    954.322P-------.272P--------4.711P-------334.446
    959.922F-------.279F--------4.722F-------333.623F
    961.887--------.274---------4.682--------335.543

    Kinda like that...

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Assumption: the unwanted character is always a single character at the end of the field.


    left([field],len([field])-1)

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or
    iif(isnumeric(right([field], 1)), [field], left([field],len([field])-1))

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Oct 2006
    Posts
    18
    It's Friday, and I won't get a chance to try this until Monday, but it sounds way easy. Being a non-vba'er, I'll assume this needs to go in a module as a function??? Give me time....I ordered the book....

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can squeeze the iif() into an update query.
    something like:

    UPDATE tblYours SET FieldABC = IIf(IsNumeric(Right([FieldABC],1)),[FieldABC],Left([FieldABC],Len([FieldABC])-1)), FieldDEF = IIf(IsNumeric(Right([FieldDEF],1)),[FieldDEF],Left([FieldDEF],Len([FieldDEF])-1));

    that will 'update' every row (i.e. 1234 will be 'updated' to 1234), and it won't be spectacularly fast.

    with only one field to be updated you could use:
    UPDATE tblYours SET FieldABC = Left([FieldABC], Len([FieldABC])-1)
    WHERE Not IsNumeric(Right([FieldABC], 1));

    in the course of your experiments, you should also test the string version of the functions: Left$() and Right$()
    ...the string functions should be around two times faster.

    izy
    currently using SS 2008R2

Posting Permissions

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