Variables, values and strings, and loops - NEWBIE

  • Thread starter Thread starter Qslx
  • Start date Start date
Q

Qslx

Variables, values and strings, and loops - NEWBIE

I'm trying to clean all trailing character codes from 0 to
127 (except 48 to 57) from the beginning and ending of each
record from field1 and tableA.
I've managed to clean the first occurence from beginning
and end of each record.
For the remaining ones (in the next positions) I've tried a
do/loop statement including the first one, such as (for
beginning of the field):


Public Function clean (field1 As Variant)

Dim fullText As Variant
fullText = Trim(field1)

Dim caracter1 As Variant
'Dim caracter2 As Variant
Dim restoTexto As Variant
caracter1 = Left(fullText, 1)
'caracter2 = Right(fullText, 1)
Dim nrAscii As Integer
nrAscii = Asc(caracter1)


Do while nrAscii < 48

Select Case nrAscii
Case 0 To 47
clean = Trim(Replace(Left(fullText,1), caracter1, "") &
Mid(fullText, 2))

loop

End Select


The system then stops working.

I appreciate any help.
Thanks.
 
You've got an infinite loop there: nothing inside the loop is changing the
value of nrAscii, so it's going to run forever.

Sounds as though you're trying to eliminate anything other than digits 0-9
from the beginning and end of the field.

Try something like:

Public Function clean (field1 As Variant)

Dim intLoop As Integer
Dim fullText As String
Dim caracter1 As String
Dim strCleanText As String

fullText = Trim(field1) & vbNullString

If Len(fullText) > 0 then
' Start looking at the first character of the string
For intLoop = 1 To Len(fullText)
caracter1 = Left$(fullText, 1)
If caracter1 < 48 _
Or caracter1 > 57 Then
' Remove the first character
fullText = Mid$(fullText, 2)
Else
' The first character is within the range
' Stop checking
Exit For
End If
Next intLoop
' Check whether there's any string left...
If Len(fullText) > 0 then
' Start looking at the last character of the string
For intLoop = Len(fullText) To 1 Step -1
caracter1 = Right$(fullText, 1)
If caracter1 < 48 _
Or caracter1 > 57 Then
' Remove the last character
fullText = Left$(fullText, intLoop - 1)
Else
' The last character is within the range
' Stop checking
Exit For
End If
Next intLoop
' Pass back what's left
Clean = fullText
Else
Clean = Null
End If

End Function
 
Qslx said:
Variables, values and strings, and loops - NEWBIE

I'm trying to clean all trailing character codes from 0 to
127 (except 48 to 57) from the beginning and ending of each
record from field1 and tableA.
I've managed to clean the first occurence from beginning
and end of each record.
For the remaining ones (in the next positions) I've tried a
do/loop statement including the first one, such as (for
beginning of the field):


Public Function clean (field1 As Variant)

Dim fullText As Variant
fullText = Trim(field1)

Dim caracter1 As Variant
'Dim caracter2 As Variant
Dim restoTexto As Variant
caracter1 = Left(fullText, 1)
'caracter2 = Right(fullText, 1)
Dim nrAscii As Integer
nrAscii = Asc(caracter1)


Do while nrAscii < 48

Select Case nrAscii
Case 0 To 47
clean = Trim(Replace(Left(fullText,1), caracter1, "") &
Mid(fullText, 2))

loop

End Select


The system then stops working.
Click to expand...

Your code never changes the value of nrAscii so the loop
runs forever.

The ascii codes 48 to 57 are the characters "0" - "9", so
(assuming you want to eliminate everything else from codes 0
through 255, not 127), then you can simplify things a
little:

Dim k As Long
Dim StartPos As Long
Dim EndPos As Long

If Len(Nz(field1, "")) = 0 Then
Clean = Null ' or maybe "" ??
Else
For k = 1 To Len(field1)
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
StartPos = k
For k = Len(field1) To 1 Step -1
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
EndPos = k

If EndPos = 0 Then
Clean = Null
Else
Clean = Mid(field1, StartPos, EndPos - StartPos + 1)
End If
 
D'oh! Much easier to read than the code I gave, Marsh! Well done.


--
Doug Steele, Microsoft Access MVP



Marshall Barton said:
Qslx said:
Variables, values and strings, and loops - NEWBIE

I'm trying to clean all trailing character codes from 0 to
127 (except 48 to 57) from the beginning and ending of each
record from field1 and tableA.
I've managed to clean the first occurence from beginning
and end of each record.
For the remaining ones (in the next positions) I've tried a
do/loop statement including the first one, such as (for
beginning of the field):


Public Function clean (field1 As Variant)

Dim fullText As Variant
fullText = Trim(field1)

Dim caracter1 As Variant
'Dim caracter2 As Variant
Dim restoTexto As Variant
caracter1 = Left(fullText, 1)
'caracter2 = Right(fullText, 1)
Dim nrAscii As Integer
nrAscii = Asc(caracter1)


Do while nrAscii < 48

Select Case nrAscii
Case 0 To 47
clean = Trim(Replace(Left(fullText,1), caracter1, "") &
Mid(fullText, 2))

loop

End Select


The system then stops working.
Click to expand...

Your code never changes the value of nrAscii so the loop
runs forever.

The ascii codes 48 to 57 are the characters "0" - "9", so
(assuming you want to eliminate everything else from codes 0
through 255, not 127), then you can simplify things a
little:

Dim k As Long
Dim StartPos As Long
Dim EndPos As Long

If Len(Nz(field1, "")) = 0 Then
Clean = Null ' or maybe "" ??
Else
For k = 1 To Len(field1)
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
StartPos = k
For k = Len(field1) To 1 Step -1
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
EndPos = k

If EndPos = 0 Then
Clean = Null
Else
Clean = Mid(field1, StartPos, EndPos - StartPos + 1)
End If
Click to expand...
 
Douglas and Marshall,
Thank you very much for you help.
Cheers,
Qslx
-----Original Message-----
D'oh! Much easier to read than the code I gave, Marsh! Well done.


--
Doug Steele, Microsoft Access MVP



Qslx said:
Variables, values and strings, and loops - NEWBIE

I'm trying to clean all trailing character codes from 0 to
127 (except 48 to 57) from the beginning and ending of each
record from field1 and tableA.
I've managed to clean the first occurence from beginning
and end of each record.
For the remaining ones (in the next positions) I've tried a
do/loop statement including the first one, such as (for
beginning of the field):


Public Function clean (field1 As Variant)

Dim fullText As Variant
fullText = Trim(field1)

Dim caracter1 As Variant
'Dim caracter2 As Variant
Dim restoTexto As Variant
caracter1 = Left(fullText, 1)
'caracter2 = Right(fullText, 1)
Dim nrAscii As Integer
nrAscii = Asc(caracter1)


Do while nrAscii < 48

Select Case nrAscii
Case 0 To 47
clean = Trim(Replace(Left(fullText,1), caracter1, "") &
Mid(fullText, 2))

loop

End Select


The system then stops working.
Click to expand...

Your code never changes the value of nrAscii so the loop
runs forever.

The ascii codes 48 to 57 are the characters "0" - "9", so
(assuming you want to eliminate everything else from codes 0
through 255, not 127), then you can simplify things a
little:

Dim k As Long
Dim StartPos As Long
Dim EndPos As Long

If Len(Nz(field1, "")) = 0 Then
Clean = Null ' or maybe "" ??
Else
For k = 1 To Len(field1)
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
StartPos = k
For k = Len(field1) To 1 Step -1
If Mid(field1, k, 1) Like "[0-9]" Then Exit For
Next k
EndPos = k

If EndPos = 0 Then
Clean = Null
Else
Clean = Mid(field1, StartPos, EndPos - StartPos + 1)
End If
Click to expand...


.
Click to expand...
 
Back
Top