Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    56

    Unanswered: worksheet_function?

    Within Access help, functions such as Substitute or Replace are listed as Worksheet_functions. One or the other I could really use them rignt now to clean up a some columns within a 10k+ records database. Minor things such as removing # or - or uneeded spaces ect.

    When it states "Worksheet_function" is it refering to excel? how can I use either substitute or replace withing an access database? Would it have to be in a macro which I dont know how to write

    Please assist. thank you

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Assuming you are doing this once to fix data in a table (or query), open the table, select field you need to change, go to edit menu, pick replace. Use the Search window to get it done-- self explanatory.

  3. #3
    Join Date
    Mar 2004
    Posts
    56
    Originally posted by poliarci
    Assuming you are doing this once to fix data in a table (or query), open the table, select field you need to change, go to edit menu, pick replace. Use the Search window to get it done-- self explanatory.
    no, not once, this is something that will have to be done multiple times which is why I am trying to automate it (in a sorts). If I could run a query (or knew how to write macros) I could do it, but if there was a way to use or plug in the substitute or replace function it would make my life so much easier right now!

  4. #4
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

  5. #5
    Join Date
    Mar 2004
    Posts
    56
    Originally posted by poliarci
    Then try something like this:
    http://www.dbforums.com/showthread.php?threadid=664107
    I would love to try that but dont know what parts to format to my table/field

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here Some more code

    Code:
    ''************ Code Start **********
    'This code was originally written by Alden Streeter.
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code Courtesy of
    'Alden Streeter
    '
    
    Function FindAndReplace(ByVal strInString As String, _
            strFindString As String, _
            strReplaceString As String) As String
    Dim intPtr As Integer
        If Len(strFindString) > 0 Then  'catch if try to find empty string
            Do
                intPtr = InStr(strInString, strFindString)
                If intPtr > 0 Then
                    FindAndReplace = FindAndReplace & left(strInString, intPtr - 1) & _
                                            strReplaceString
                        strInString = Mid(strInString, intPtr + Len(strFindString))
                End If
            Loop While intPtr > 0
        End If
        FindAndReplace = FindAndReplace & strInString
    End Function
    
    '************ Code End **********
    Paste the above code into Module
    and save it

    make shore there are no red lines


    Build a new Query


    in the Feild coloum


    Expr1: FindAndReplace([feildname],"*","")

    so this will find all the * in the [feildname] and replace it with a blank

    if you don't understand

    a send me a sample of you database


    IF YOUR ARE USING A2000+

    YOU CAN JUST USE THE REPLACE([FEILDNAME],FINDTHIS,REPLACEIT)
    Last edited by myle; 04-06-04 at 18:16.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Mar 2004
    Posts
    56
    Originally posted by myle
    Here Some more code

    Code:
    ''************ Code Start **********
    'This code was originally written by Alden Streeter.
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code Courtesy of
    'Alden Streeter
    '
    
    Function FindAndReplace(ByVal strInString As String, _
            strFindString As String, _
            strReplaceString As String) As String
    Dim intPtr As Integer
        If Len(strFindString) > 0 Then  'catch if try to find empty string
            Do
                intPtr = InStr(strInString, strFindString)
                If intPtr > 0 Then
                    FindAndReplace = FindAndReplace & left(strInString, intPtr - 1) & _
                                            strReplaceString
                        strInString = Mid(strInString, intPtr + Len(strFindString))
                End If
            Loop While intPtr > 0
        End If
        FindAndReplace = FindAndReplace & strInString
    End Function
    
    '************ Code End **********
    Paste the above code into Module
    and save it

    make shore there are no red lines


    Build a new Query


    in the Feild coloum


    Expr1: FindAndReplace([feildname],"*","")

    so this will find all the * in the [feildname] and replace it with a blank

    if you don't understand

    a send me a sample of you database


    IF YOUR ARE USING A2000+

    YOU CAN JUST USE THE REPLACE([FEILDNAME],FINDTHIS,REPLACEIT)
    This looks exactly to what I need, I did as you instructed but got the error "undefined function FindAndReplace" So I am assumeing I did somethig wrong.

    I am attaching an very small example of what I am trying to do. In the one field within the table I have a great deal of "Garbage" such as C/O, ATT, ATTN:, ATTN and so on. I need to remove these things without harming the good data such as the vendor or contact name within the record.

    I greatly appriciate all your help on this, I am stuck on this one function and past deadline!
    Attached Files Attached Files

Posting Permissions

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