Thread: Replace Function Problems
09-21-06, 21:11 #1Registered User
- Join Date
- Sep 2006
Unanswered: Replace Function Problems
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...
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","").
09-22-06, 09:18 #2Grand Poobah
- Join Date
- Sep 2003
Question: Does every description have a date at the end? Is this date ALWAYS 5 characters?
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 ...