VB beginner having problems with a function

Dachande11

Registered User.
Local time
Today, 10:05
Joined
May 1, 2001
Messages
41
Here is my problem:-
I need a function to check for unwanted characters in a field (for example ("," " ",""") and then replace them with a single space.
I think I have found a piece of code from an old employee which was designed for this purpose, but being a total beginner I do not understand why it is not working and what I need to do to fix it. Here is the code:

Option Compare Database
Option Base 1
Public Function AddrCheck(sString As String)
Dim ChkCharArray() As Variant


ChkCharArray(1) = " "
ChkCharArray(2) = Chr(39) ' '
ChkCharArray(3) = Chr(44) ' ,
ChkCharArray(4) = Chr(34) ' "

X = 1

For c = 1 To Len(sString)

Y = 1 'Sets starting position of array

For z = 1 To UBound(ChkCharArray)
sString = Replace(sString, ChkCharArray(Y), " ")
Y = Y + 1
Next z

X = X + 1

Next c


End Function


I thank in advance for anyone who can help me.

Mark
 
hi Mark,

can you post the Replace code as well ( i'm assuming this is not a built in 2k function i don't know about? ). Apart from the odd ( and undeclared ) use of z & y, and the complete non-use of X it's difficult to say ( and they aren't going to cause errors anyhow ). There doesn't seem to be a return value either ( eg AddrCheck=sString ),

regards

Drew

[This message has been edited by KDg (edited 07-24-2001).]
 
Hi Drew, sorry but this is all i have.
This is what i thought initially but as i don't know much VB i just thought he knew better.

Thanks
Mark
 
No problem, i'm sure people here can build you a replacement if required. Have you tried searching the database for Replace? If you move your mouse over it in the code and right click it should give you a "Definition" option down the bottom. If you click it, it should take you to some code. If it does then post it here, if not then post back and i'll do you a new version,

HTH

Drew
 
Hi Drew

I clicked on the definition but it did not bring any code up. If you could help I would be very grateful, if not thanks for trying anyway.

Mark
 
Hi Mark,

this is fairly rough and ready so i'll keep looking at it, it seems to work, but as it's a first write i'd be amazed if it does. Nothing ever works completely the first time i do it...
Code:
Public Function AddrCheck(sString As String) As String
Dim ChkCharArray(2) As String
Dim c As Integer
Dim z As Integer
Dim y As Integer
    ChkCharArray(0) = Chr(39) ' '
    ChkCharArray(1) = Chr(44) ' ,
    ChkCharArray(2) = Chr(34) ' "

    For c = 1 To Len(sString)
        For z = 0 To UBound(ChkCharArray)
            If InStr(c, sString, ChkCharArray(z)) Then
                y = InStr(c, sString, ChkCharArray(z))
                sString = Left(sString, y - 1) & Chr(32) & Mid(sString, y + 1, Len(sString))
            End If
        Next z
Next c
AddrCheck = sString
End Function

I'll post again once happy with it - if anyone else has a better solution please post it, i'm sure there are better examples out there on VB websites,

HTH

Drew
Additonal: This currently replaces the unwanted ' , " with a space - is that what you were after or do you want to be able to specify the replacing character?


[This message has been edited by KDg (edited 07-24-2001).]

Okay, i've edited the above a couple of times to tidy it up, otherwise i think i'm happy, let me know if it works, cheers, Drew

[This message has been edited by KDg (edited 07-24-2001).]
 
Hi Drew,

I just tried it and it came back with a
runtime error 9
Subscript out of range

I have to go now but I will try anything else in the morning.

Thanks again

Just a straight replace with a space is fine.
I am just going to try it now.

Mark

Mark

[This message has been edited by Dachande11 (edited 07-24-2001).]
 
runs fine here - you may have got a part edited version, i should really know better than to edit code on screen here without testing again. If you copy it again you i think you'll be okay, if not post again

Drew
 
Hi Drew

It came back with the subscript out of range again.

Maybe it is something I am doing wrong

I will try again in the morning

Thanks

Mark
 
Hi again Drew,
Thanks for this, it works perfectly.
But I have one more question.
How do I get it to check for double spaces and replace them with a single space?

Thanks and sorry to bother you again

Mark
 
Try this function:

Public Function AddrCheck(sString As String) As String
Dim ChkCharArray(3)
Dim I As Integer

'ChkCharArray elements can only be a single character or the mid "statement" won't work
ChkCharArray(0) = Chr(32) ' single space - replacement character for comma,squote or dquote
ChkCharArray(1) = Chr(39) ' ' single quote
ChkCharArray(2) = Chr(44) ' , comma
ChkCharArray(3) = Chr(34) ' " double quote

For I = 1 To UBound(ChkCharArray)

While InStr(1, sString, ChkCharArray(I))
'mid to the left of equal sign is mid "statement"...
Mid(sString, InStr(1, sString, ChkCharArray(I)), 1) = ChkCharArray(0)
Wend

Next I

'eliminate double spaces which may be caused by above for/next...
While InStr(1, sString, " ") ' double space between quotes in this while/wend
'mid to the right of equal sign is mid "function"...
sString = Mid(sString, 1, InStr(1, sString, " ")) & Mid(sString, InStr(1, sString, " ") + 2)
Wend

'get rid of leading/trailing spaces...
sString = Trim(sString)

'done
AddrCheck = sString
End Function

Get rid of the Option Base 1 statement or you have to change the array indexing around...

A word of caution: be careful trying to modify the above code, especially between the while/wend statements - it's easy to cause infinite loops...

Doug.

[This message has been edited by DALeffler (edited 07-25-2001).]
 
Hi Mark,

here's a double space stripping version. I't's getting uglier but it still works so who cares

Code:
Public Function AddrCheck(sString As String) As String
Dim ChkCharArray(3) As String
Dim c As Integer
Dim z As Integer
Dim y As Integer

    ChkCharArray(0) = Chr(39) ' '
    ChkCharArray(1) = Chr(44) ' ,
    ChkCharArray(2) = Chr(34) ' "
    'treat spaces as a special case...
    ChkCharArray(3) = Chr(32)
'    ChkCharArray(3) = Chr(34) ' "
    For c = 1 To Len(sString)
        For z = 0 To UBound(ChkCharArray)
            If InStr(c, sString, ChkCharArray(z)) Then
                y = InStr(c, sString, ChkCharArray(z))
                'found a wotsit, check the space ahead, if it's a space then just strip it, otherwise change to a space
                If StrComp(Mid(sString, y + 1, 1), Chr(32)) = 0 Then
                    sString = Left(sString, y - 1) & Mid(sString, y + 1, Len(sString))
                Else
                    ' line bleow changed after reading doug's code above, how did i not know you could do this?
                     Mid(sString, y, 1) = Chr(32)
                End If
                
            End If
        Next z
Next c
AddrCheck = sString
End Function

Don't worry about asking questions - if you were bothering me i wouldn't be replying,

HTH

Drew

[This message has been edited by KDg (edited 07-25-2001).]
 
Thanks Drew

Everything works great!
Keep up the good work!

Mark
 

Users who are viewing this thread

Back
Top Bottom