using ASC() to strip out invalid values

[Guest]

Hello all,
I am using this function in an update query to try and strip out invalid
characters in a memo field. The problem is when I export this memo field to
Excel, it corrupts excel. I'm pretty sure the problem is with invalid
characters in the memo field.

Error 6 (Overflow) on line 20

What am i doing wrong?

Public Function GetAlphaString(strMyString) As String

Dim i As Integer
Dim iAsc As Integer


20 For i = 1 To Len(strMyString)
30 iAsc = Asc(Mid(strMyString, i, 1))
40 If (iAsc >= 32 And iAsc <= 123 = 0) Then
50 GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
60 Exit For
70 End If
80 Next i

 

[Guest]

sorry, typo in my example on line 40... but same error 6 on line 20

Dim i As Integer
Dim iAsc As Integer

20 For i = 1 To Len(strMyString)
30 iAsc = Asc(Mid(strMyString, i, 1))
40 If (iAsc >= 32 And iAsc <= 123) Then
50 GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
60 Exit For
70 End If
80 Next i

 

[Jeff Boyce]

David

I may not be reading your code correctly, but it seems to me that after
checking the nth character in the input string, you'd want to save it if it
were in the range you seek. Using the "Right()" function would seem to be
saving something else.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

David

And a bit more... It appears that your function returns a value after only
one check -- don't you want to "build" a new string before returning it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
Thank you for your response, unfortunately this is way over my head.. i'm
just trying to grab snipits of code.

Any thoughts about how to strip non alpha and non numeric and non nulls from
a memo field? iAsc >= 32 And iAsc <= 123 or iAsc = 0

 

[John W. Vinson]

Error 6 (Overflow) on line 20

What am i doing wrong?

Public Function GetAlphaString(strMyString) As String

Dim i As Integer
Dim iAsc As Integer


20 For i = 1 To Len(strMyString)

An Integer is limited to 2^15, 65536. If the memo is longer than this you'll
get the error.

Dim i as Long will let you handle any memo field.

John W. Vinson [MVP]

 

[Jeff Boyce]

David

You might need to plan in some time to learn about what the code is doing,
otherwise, how will you know if it's doing what YOU want?

Here's my take on what you described (no code, just steps...):

* Start with the full string
* Start at the left-most character
* Check to see if it is in your required range
* If it is, save that character to a new string you are building and skip
the following step
* If it isn't, move to the next step
* Have you reached the end of your rope (er, string)?
* If not, move over one character and start over at the third step above
* If so, write/store/do something with the string you've built up.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

An Integer is limited to 2^15, 65536. If the memo is longer than this you'll
get the error.

Last time I checked, 2^15 equalled 32768 ;-)

Jamie.

--

 

[John W. Vinson]

Last time I checked, 2^15 equalled 32768 ;-)

Jamie.

oops... ran out of fingers and should have taken off my socks!

John W. Vinson [MVP]