Re: [AccessDevelopers] Replace: How many # has made

[A.D.Tejpal]

Julius,       Apparently, you wish to replace all instances of "One" by "Single" and "Two" by "Many" and also keep track of the number of such replacements carried out in each record.       Sample query given below should be able to get you the required output. It would show the original text, revised text (after implementing replacements as above) and the count of replacements.       T_Data is the name of table.  Fn_ReplaceOneAndTwo() and Fn_CountReplaceOneAndTwo() are the two user defined functions used in the query.   Best wishes, A.D.Tejpal   Sample Query (Using functions given below) ================================= SELECT T_Data.OriginalText, Fn_ReplaceOneAndTwo([OriginalText]) AS RevisedText, Fn_CountReplaceOneAndTwo([OriginalText]) AS CountReplace  FROM T_Data; =================================   User Defined Functions (used in sample query above) =================================   Function Fn_CountReplaceOneAndTwo(ByVal _                                   OriginalText As String) As Long     Dim Txt As String, Cnt As Long         Txt = OriginalText     Cnt = UBound(Split(Txt, "One"))     Cnt = Cnt + UBound(Split(Txt, "Two"))         Fn_CountReplaceOneAndTwo = Cnt     End Function ---------------------------------------------------------   Function Fn_ReplaceOneAndTwo(ByVal _                                   OriginalText As String) As String     Dim Txt As String         Txt = OriginalText     Txt = Replace(Txt, "One", "Single")     Txt = Replace(Txt, "Two", "Many")         Fn_ReplaceOneAndTwo = Txt     End Function =================================   ----- Original Message ----- From: uhitmiaysuu To: AccessDevelopers@yahoogroups.com Sent: Thursday, October 20, 2005 03:04 Subject: [AccessDevelopers] Replace: How many # has made Hi, I have a memo field of letters and I changed the character names from: "two" as "many" and "one" as "single". But I don't how to calculate how many has been change. Sample text: One Apple Two Oranges Two Pears 'my sample code: Function FindRepl(x) Dim Rep1, Rep2 As String Rep1 = x Rep2 = Rep1 Rep1 = Replace(x, "Two", "Many") 'how can I calc how many replace of Rep1 has been changed Rep2 = Replace(Rep1, "One", "Single") FindRepl = Rep2 End Function Thanks, Julius '2002 Please zip all files prior to uploading to Files section. YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.