By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

Extracting Numeric Strings Problem

P: 5
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
Share this Question
Share on Google+
3 Replies


Expert
P: 97
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

P: 5
:) YOU'RE A STAAARRRRR

My manager likes you
May 17 '07 #3

Expert
P: 97
:) 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.