467,113 Members | 1,326 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,113 developers. It's quick & easy.

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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by success_ny@yahoo.com | last post: by
7 posts views Thread by Raphi | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.