Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: replace function

    What is the equivalent function for replace in MS Access

  2. #2
    Join Date
    Jul 2003
    Posts
    48

    Re: replace function

    Do you mean access query or vba in access

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    Re: replace function

    Originally posted by bscorer
    Do you mean access query or vba in access
    I'ld just like to hijack this question...

    What is the equivalent Excel replace function in Access in a query (ideally).

    Am trying to remove intermittant spaces from content of a text field.

    Thanks

    RKT

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    top-of-the-head effort to fake a replace in code:

    function fakeReplace(strTarget as string, strVictim as string, strVictor as string) as string

    'strVictim: string to be replaced
    'strVictor: the replacement string
    'strTarget: potentially contains victims to be replaced with victors

    'could use sub with strTarget cited by-reference, but func
    'allows greater scope for adding error handling later and
    'returning meaningful error messages without destroying
    'the original strTarget in an error condition

    dim intPointer as integer 'a position in strTarget
    dim strWorking as string 'a string

    strWorking = strTarget
    intPointer = 1 'start somewhere

    do while true
    intPointer = instr(intPointer, strWorking , strVictim)
    if intPointer = 0 then exit loop
    strWorking = left$(strWorking , intPointer -1) & strVictor & mid$(strWorking , intPointer + len(strVictim))
    loop

    fakeReplace = strTarget

    exit function

    this might be out by +/- a character or two, but something like this should work.

    izy

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    second thoughts about this bit of code: imagine
    strVictor = "123456"
    strVictim = "123"

    strTarget = "test123test"
    1st loop: test123456test
    2nd loop: test123456456test
    3rd loop: test123456456456test
    ...and so on until you bounce off memory/string-length/patience limits

    therefore, possibly safer:
    Code:
    function fakeReplace2(strTarget as string, strVictim as string, strVictor as string) as string
      dim intPointer as integer 'a position in strTarget
      dim strWorking as string 'a string
      if strVictim = strVictor then
        fakeReplace2 = strTarget
        exit function
      endif
      strWorking = strTarget 'protect strTarget
      intPointer = 1 'start somewhere
      do while true
        intPointer = instr(intPointer, strWorking , strVictim) 'find first strVictim
        if ((intPointer = 0) or (isnull(intPointer))) then exit loop 'done if not found
        strWorking = left$(strWorking , intPointer -1) & strVictor & mid$(strWorking , intPointer + len(strVictim))
        intPointer = intPointer + len(strVictor) -1 'start looking after last replace
      loop
      fakeReplace2 = strWorking 
    exit function
    izy
    Last edited by izyrider; 10-12-03 at 11:56.

Posting Permissions

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