Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Unanswered: Replace Function Problems

    Hi Everyone,

    Sorry to bother you all, but I have scoured the internet and MS Access help pages and after several hours don't seem to be making any progress...

    I'm using MS Access 2003, and the field I'm referring to is text...

    pmdesc
    DVD REDWALL VOL5 12/07
    DVD RELIC HUNTER SEASON 2 21/06

    This is an example of some of the data in the database field pmdesc. I need to take the DVD out of the start of the description and get rid of the date on the end.

    If I do a manual find/replace for DVD and ##/## I can get rid of them both no problems that way. I just need to find a way to do it automatically. (Other titles start with similar things, such as UMD titles having UMD at the front, soundtrack CDs having S/T at the front, and so forth.)

    I tried creating an update query with the replace function but have not gotten it even close to working! Can someone possibly shed some light on what I am doing wrong?

    I was using: Replace([pmdesc], "##/##", "") and Replace([pmdesc],"DVD","").

    Regards,
    Andrew

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Question: Does every description have a date at the end? Is this date ALWAYS 5 characters?

    Stage 1:

    UPDATE WhatEverTable SET pmdesc=IIF(LEFT(pmdesc,3)='DVD',RIGHT(pmdesc,LEN(p mdesc)-4),pmdesc);

    This will handle your "DVD" problem ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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