467,113 Members | 1,326 Online

# Extracting Numeric Strings Problem

 Hello People, I am a newbie here, and if I make some stupid mistakes please forgive me :) I have found this site very helpful, and was hoping someone could solve a problem I have. I have created a module in Access, Office 2003. The module is supposed to extract numeric strings from a field on a form and place entries in a sub form. The code below is how I have tried to do this... Option Compare Database Public Function FindUrns_Click() Dim dbs As Database Set dbs = CurrentDb Dim TPURN As String Dim URN As String Dim URNLen As Integer Dim Lp As Integer Forms!URNS!URNS.SetFocus DoCmd.GoToRecord , , acFirst URN = Forms!URNS!URNS Lp = Len(URN) 'Start loop for record strings Do While Lp > 1 Forms!URNS!URNS.SetFocus URN = Forms!URNS!URNS URNLen = Len(URN) If IsNumeric(Left(Forms!URNS!URNS, 1)) Then Do While IsNumeric(Left(Forms!URNS!URNS, 1)) URNLen = Len(URN) TPURN = TPURN & (Left(Forms!URNS!URNS, 1)) Forms!URNS!StringsSubform!NumericString = TPURN Forms!URNS!URNS = Right(Forms!URNS!URNS, URNLen - 1) Loop Forms!URNS!StringsSubform.SetFocus DoCmd.GoToRecord , , acNext Else Forms!URNS!URNS = Right(Forms!URNS!URNS, URNLen - 1) Forms!URNS!StringsSubform!NumericString = Forms!URNS!StringsSubform!NumericString & TPURN End If Lp = Len(URN) Loop 'End Loop End Function The problem is when a numeric string is found the second number is repeatedly entered as the do while loop seems to get stuck, causing and error as the maximum field size gets reached as the module doesn't move through the string, I don't understand why this is. Please help I cannot see where I am going wrong!! Thanks in advance. May 17 '07 #1
• viewed: 1210
Share:
3 Replies
 Expert As I understand it, when you use Forms!URNS!URNS it reads the current value, but does not update it until you exit the control. So in effect you are always reading the same character. You have the line URN = Forms!URNS!URNS so parse the variable URN instead URN = Right(URN, URNLen - 1) May 17 '07 #2
 :) YOU'RE A STAAARRRRR My manager likes you May 17 '07 #3
 Expert :) YOU'RE A STAAARRRRR My manager likes you Glad to help! Thanks for the compliment. May 17 '07 #4