Results 1 to 15 of 15

Thread: Replace problem

  1. #1
    Join Date
    Mar 2004
    Posts
    56

    Unanswered: Replace problem

    in Access 2003 the replace expression is as follows:

    Syntax
    Replace(expression, find, replace, start, count, compare)
    Note If start is omitted, 1 is assumed. If the number of substring substitutions is omitted (count), the value— 1 is assumed which means to perform all possible substitutions. For an explanation of the types of comparisons provided in Access, see Figure 1 above.


    Example
    Replace("C:\MyFiles\", "My", "Your") returns the string "C:\YourFiles\".


    I am using the formula

    Replace("ATTN:","ATTN:","")

    within a column/field to remove the ATTN: from the field. in a table of 3000 records there are 10 with ATTN: in it (I am writting an expression as I will need to run this multiple times so want to automate it). I make an update query from the original table to a cleand-up table and it keeps comming up with 0 records changed when I know there are 10 in there. What am I missing here?

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Your replace function is working, just not as intended.

    I believe your intent is to change "ATTN: Mike" to " Mike" or "Mike", but what you're actually doing is creating a text value then replacing it. If you were to type
    Code:
    Replace("ATTN:","ATTN:","")
    in your QBE, you'll just get a blank column. It is similar to looking for "ATTN:" by just using this as an expression, you will just get "ATTN:" for each row of data your query otherwise returns.

    What you actually want is to have your field name in place of expression, not a text literal:

    Code:
    Replace([YourFieldName],"ATTN:","")
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Replace problem

    Originally posted by roma92
    in Access 2003 the replace expression is as follows:

    Syntax
    Replace(expression, find, replace, start, count, compare)
    Note If start is omitted, 1 is assumed. If the number of substring substitutions is omitted (count), the value— 1 is assumed which means to perform all possible substitutions. For an explanation of the types of comparisons provided in Access, see Figure 1 above.


    Example
    Replace("C:\MyFiles\", "My", "Your") returns the string "C:\YourFiles\".


    I am using the formula

    Replace("ATTN:","ATTN:","")

    within a column/field to remove the ATTN: from the field. in a table of 3000 records there are 10 with ATTN: in it (I am writting an expression as I will need to run this multiple times so want to automate it). I make an update query from the original table to a cleand-up table and it keeps comming up with 0 records changed when I know there are 10 in there. What am I missing here?
    Along the same lines as what HomerBoo posted..

    This worked in a query for me. I tried it as a separate calculated field in a select query and as the "Update To" expression in an update query.

    Replace([tblTeamsTest].[TeamName],"Officers","Ducks").
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Mar 2004
    Posts
    56

    Re: Replace problem

    Originally posted by basicmek
    Along the same lines as what HomerBoo posted..

    This worked in a query for me. I tried it as a separate calculated field in a select query and as the "Update To" expression in an update query.

    Replace([tblTeamsTest].[TeamName],"Officers","Ducks").
    Theres a bit more of a problem, for example in one of the fields there may be "ATTN:John Doe", now if I try the replace it comes up with 0 hits on ATTN:, same if I do a find in the column for ATTN:. Although, if I do the same find/search for *ATTN:* then it finds it. But, I cant use *ATTN:* within the find and replace as if I do it will delete the entire field rather than just the ATTN:.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Replace problem

    Originally posted by roma92
    Theres a bit more of a problem, for example in one of the fields there may be "ATTN:John Doe", now if I try the replace it comes up with 0 hits on ATTN:, same if I do a find in the column for ATTN:. Although, if I do the same find/search for *ATTN:* then it finds it. But, I cant use *ATTN:* within the find and replace as if I do it will delete the entire field rather than just the ATTN:.
    Sorry, I was thinking that you wanted to replace the whole thing.

    You might try to create a subquery to isolate the records the you want to change and then manipulate the update queries "Update To" cell with Right, Left and Len functions to remove the ATTN.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Mar 2004
    Posts
    56
    Originally posted by HomerBoo
    Your replace function is working, just not as intended.

    I believe your intent is to change "ATTN: Mike" to " Mike" or "Mike", but what you're actually doing is creating a text value then replacing it. If you were to type
    Code:
    Replace("ATTN:","ATTN:","")
    in your QBE, you'll just get a blank column. It is similar to looking for "ATTN:" by just using this as an expression, you will just get "ATTN:" for each row of data your query otherwise returns.

    What you actually want is to have your field name in place of expression, not a text literal:

    Code:
    Replace([YourFieldName],"ATTN:","")
    Homer, I tried this, problem being is in doing the search since the field has the test ATTN:Mike with no space the search criteria is not seeing the ATTN: it is looking literally so I would have to have the criteria as ATTN:Mike for it to find it. In other words

    Replace([YourFieldName],"ATTN:","")

    returns nothing as its not seeing ATTN: but rather ATTN:Mike.

    Doing a find and replace shouldnt be this much of a hassle~!

    I also tried Right("ATTN:",5) this returned nothing as well, although I dont know why.

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by roma92
    Homer, I tried this, problem being is in doing the search since the field has the test ATTN:Mike with no space the search criteria is not seeing the ATTN: it is looking literally so I would have to have the criteria as ATTN:Mike for it to find it. In other words

    Replace([YourFieldName],"ATTN:","")

    returns nothing as its not seeing ATTN: but rather ATTN:Mike.

    Doing a find and replace shouldnt be this much of a hassle~!

    I also tried Right("ATTN:",5) this returned nothing as well, although I dont know why.
    I know you directed this at Homer but it was the last post so here I am.

    Are you hung on the Replace function? Just for experimentation purposes, I created some fields in a table that were similar to what you had described. I then used an update query to do the dirty work.

    I used the like "ATTN:*" in the criterea cell and

    Trim(Right(tblEmployee.EmpLocation,Len(tblEmployee .EmpLocation)-5))

    in the Update To cell.


    It worked no matter how many spaces I had in there. Tried it several times and it always returned what I expected.

    Of course, if the replace function is what you want to use then this won't be of much use to you. Just felt like experimenting.

    Hope it helps or gives you some additional ideas.
    Last edited by basicmek; 04-06-04 at 22:03.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  8. #8
    Join Date
    Mar 2004
    Posts
    56
    Originally posted by basicmek
    I know you directed this at Homer but it was the last post so here I am.

    Are you hung on the Replace function? Just for experimentation purposes, I created some fields in a table that were similar to what you had described. I then used an update query to do the dirty work.

    I used the like "ATTN:*" in the criterea cell and

    Trim(Right(tblEmployee.EmpLocation,Len(tblEmployee .EmpLocation)-5))

    in the Update To cell.


    It worked no matter how many spaces I had in there. Tried it several times and it always returned what I expected.

    Of course, if the replace function is what you want to use then this won't be of much use to you. Just felt like experimenting.

    Hope it helps or gives you some additional ideas.

    basicmek, for starters I want to thank you for all the assistance and valuable time you have offered me. Your function works great if my fields all started with ATTN:. Its my own fault for not being clear to which I appologize.

    I am attaching a small clipped example, within a record(one of many) of a field which is 10s of thousands large(record wise) there are things such as ATT, ATTN:, C/O, D/B/A. dba, d/b/a and on and on. I need to hold the integrity of the valued data such as the name of the vendor (ACME) but loose the 'garbage' (ACME, Inc) or (D/B/A ACME) or (ACME C/O) ect...

    thank you again. This project has me stuck until I can get this cleaned which should be easy but my knowledge of access isnt what it needs to be.

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by roma92
    basicmek, for starters I want to thank you for all the assistance and valuable time you have offered me. Your function works great if my fields all started with ATTN:. Its my own fault for not being clear to which I appologize.

    I am attaching a small clipped example, within a record(one of many) of a field which is 10s of thousands large(record wise) there are things such as ATT, ATTN:, C/O, D/B/A. dba, d/b/a and on and on. I need to hold the integrity of the valued data such as the name of the vendor (ACME) but loose the 'garbage' (ACME, Inc) or (D/B/A ACME) or (ACME C/O) ect...

    thank you again. This project has me stuck until I can get this cleaned which should be easy but my knowledge of access isnt what it needs to be.
    Your welcome. I enjoy a challenge anyway.

    A few more thoughts though:

    How many different variations are there?

    Could you create a table that included the different variations?

    I have done some things in the past with text data to format it and remove things using a table to store the things that I was looking for.

    If this idea interests you let me know.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  10. #10
    Join Date
    Mar 2004
    Posts
    56

    Example

    attached (I hope) is an example..
    [edit]

    I have tried to attach my example here but it wont upload, here is a link to the same example I attached to another post.

    http://www.dbforums.com/showthread.php?threadid=991813

    Its the last post in the thread
    Thanks again
    Last edited by roma92; 04-07-04 at 10:43.

  11. #11
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Example

    Originally posted by roma92
    attached (I hope) is an example..
    [edit]

    I have tried to attach my example here but it wont upload, here is a link to the same example I attached to another post.

    http://www.dbforums.com/showthread.php?threadid=991813

    Its the last post in the thread
    Thanks again
    Is it doable to create a list of the "Garbage" that you need removed?

    For instance, I see the % sign, the # sign, ATTN:, ATT:, ATTN, and ATTN.:

    I can help you write a function that compares each value in the field that you are wanting to correct with the values in a list that you create (this would be the table of garbage. If it sees a match then it modifies the original value accordingly and can remove spaces that are left. When you find more "Garbage" after removing some of it, you can simply add to the list of stuff you want removed. It works quite nicely and allows you to progressively remove things as you discover them. It will be a bit easier if the piece you are trying to remove is at the beginning consistantly but either way can be done.

    In an update query the criterea would just be calling the function which would do all of the work.

    If this sounds like it could work for you, let me know.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  12. #12
    Join Date
    Mar 2004
    Posts
    56

    Re: Example

    Originally posted by basicmek
    Is it doable to create a list of the "Garbage" that you need removed?

    For instance, I see the % sign, the # sign, ATTN:, ATT:, ATTN, and ATTN.:

    I can help you write a function that compares each value in the field that you are wanting to correct with the values in a list that you create (this would be the table of garbage. If it sees a match then it modifies the original value accordingly and can remove spaces that are left. When you find more "Garbage" after removing some of it, you can simply add to the list of stuff you want removed. It works quite nicely and allows you to progressively remove things as you discover them. It will be a bit easier if the piece you are trying to remove is at the beginning consistantly but either way can be done.

    In an update query the criterea would just be calling the function which would do all of the work.

    If this sounds like it could work for you, let me know.
    Sounds like it would thank you, here is what I have dug through and on a majority.

    #
    DIST
    DEPT
    DBA
    dba
    CO.
    &
    D/B/A
    C/O
    ATTN:
    ATTN
    ATT
    ATT:
    INC
    %
    .(period, no space)

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Example

    Originally posted by roma92
    Sounds like it would thank you, here is what I have dug through and on a majority.

    #
    DIST
    DEPT
    DBA
    dba
    CO.
    &
    D/B/A
    C/O
    ATTN:
    ATTN
    ATT
    ATT:
    INC
    %
    .(period, no space)
    Are these all values found at the beginning of the field or do you need to look at the whole field?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  14. #14
    Join Date
    Mar 2004
    Posts
    56

    Re: Example

    Originally posted by basicmek
    Are these all values found at the beginning of the field or do you need to look at the whole field?
    Unfortunatly they can be in the middle or end as well as front, so the whole field.

  15. #15
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I apologise for being away and not answering this question in a timely manner.

    Homer, I tried this, problem being is in doing the search since the field has the test ATTN:Mike with no space the search criteria is not seeing the ATTN: it is looking literally so I would have to have the criteria as ATTN:Mike for it to find it. In other words
    The test is not actually ATTN:Mike or ATTN: Mike, ATTN:Mike is a sample value contained within a row of the column YourFieldName that Replace([YourFieldName], "ATTN:", "") works on.

    In LastName, I had the following Data:
    LastName
    ATTN:1
    ATTN:2
    ATTN:3
    ATTN:4
    ATTN:5
    ATTN:6
    ATTN:7
    ATTN:8
    ATTN:9
    ATTN:10
    to your ATTN:10

    In the query, I used the replace function with the following criteria:
    Replace([LastName],"ATTN:","")

    The following is the result (see UserDefFn below):
    Code:
    LastName	ReplaceFn	ReplaceIt_UserDefFn
    LN	LN	LN
    LN1	LN1	LN1
    ATTN:1	1	1
    ATTN:2	2	2
    ATTN:3	3	3
    ATTN:4	4	4
    ATTN:5	5	5
    ATTN:6	6	6
    ATTN:7	7	7
    ATTN:8	8	8
    ATTN:9	9	9
    a ATTN:10	a 10	a 10
    ATTN:10	10	10
    Is this not what you were looking for?

    Additionally, if you actually need to replace a whole buncha set words that may or may not be within your data, I would recommend that you write a user-defined function, and call that function from your query. You can use Replace in there and have the function return your string ex the thing you wanted to replace:

    Code:
    Function ReplaceIt(strFieldValue As String) As String
    
        Dim strReplaceArr(13) As String
        Dim strReturnString As String
        Dim i As Integer
        
        strReturnString = strFieldValue
        strReplaceArr(0) = "#"
        strReplaceArr(1) = "DIST"
        strReplaceArr(2) = "DEPT"
        strReplaceArr(3) = "dba"
        strReplaceArr(4) = "CO."
        strReplaceArr(5) = "&"
        strReplaceArr(6) = "D/B/A"
        strReplaceArr(7) = "C/O"
        strReplaceArr(8) = "ATTN:"
        strReplaceArr(9) = "ATTN"
        strReplaceArr(10) = "ATT"
        strReplaceArr(11) = "ATT:"
        strReplaceArr(12) = "INC"
        strReplaceArr(13) = "%"
        
        
        For i = 0 To 13
            If InStr(strFieldValue, strReplaceArr(i)) Then ' IF I CAN FIND SOMETHING TO REPLACE
                strReturnString = Replace(strFieldValue, strReplaceArr(i), "")
                Exit For ' USE THIS IF YOU DON'T WANT TO ITERATIVELY SEARCH FOR YOUR REPLACMENTS, ELSE TAKE IT OUT
            End If
        Next i
        
        ReplaceIt = strReturnString
        Erase strReplaceArr
    
    End Function
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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