Macro to remove ";"

[JA]

This macro suggested for my previous question is
returning the error sub or function not defined.
Can some tweak it for me.

Sub Test()

Dim NewStr As String

NewStr = Replace(MyStr, ";;", ";")
If Mid(NewStr, 1, 1) = ";" Then NewStr = Right
(NewStr, Len(NewStr) - 1)
If Right(NewStr, 1) = ";" Then NewStr = Left(NewStr,
Len(NewStr) - 1)
If InStr(NewStr, ";;") Then NewStr = Clean(NewStr)
SemiClean = NewStr


End Sub



My field CombMod in huge database contains entries with
unnecessary semi colons in different places. I need a
macro to remove them.

For Eaxmple
;PM;;;LE;;;;; to become PM;LE

;PM;;PP PL;PP OP;PP PK;;;;;;QM to become PM;PP PL;PP
OP;PP PK;QM

Please help

 

[Roger Carlson]

First of all, this is not a macro, it's code. I know that in Excel macros
ARE code, but in Access macros and code modules are very different things.
Semantics maybe, but terminology is important when trying to communicate.

As for the error, the Replace() function wasn't introduced until Access
2000, so if you are using Access 97, you can't use it. It looks like
Brendan's function will work in any version of Access.

If you ARE using Access 2000 or above, you might be having a problem with
your References. Sometimes if you have a MISSING reference, the error will
show up in other libraries and not the one that is missing. In a code
window, go to Tools > References and see if any are MISSING. Uncheck that
one and close the window. This usually fixes the problem. If it doesn't,
look here for more information:
http://members.iinet.net.au/~allenbrowne/ser-38.html
or
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L