how to do search in vba code

Hi All,

first, to store data this way was not my idea, this is what I have to
work with, which is, a extract from a mainframe system which I import
into an Access2003 table. What I have to do is search for one fo these
characters

"~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/","\","="

in the value of the Full Name field and strip it off and then write
the new value to the Full Name field in another table.

examples of imported name field

BITRAN+,DANI
BLUMENKOPF,BENNETT#
CLARK=,PAUL R.
%PARISH,MICHAEL A.

I'm thinking the only way to do this is with a if inside a loop but I
didn't want to have to write the if as a if elseif elseif for each
special character. I was also hoping not to have to write Case logic
for each special character either.

Any ideas on the vba code to do this?
are the numeric codes for speacial characters in a range? maybe
there's some way to use a between 021 and 534. oh, what a project
this is.....
bobh.

On Jun 11, 1:08*pm, bobh <vulca...@yahoo.com> wrote:
> Hi All,
>
> first, to store data this way was not my idea, *this is what I have to
> work with, which is, a extract from a mainframe system which I import
> into an Access2003 table. What I have to do is search for one fo these
> characters
>
> "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/"*,"\","="
>
> in the value of the Full Name field and strip it off and then write
> the new value to the Full Name field in another table.
>
> examples of imported name field
>
> BITRAN+,DANI
> BLUMENKOPF,BENNETT#
> CLARK=,PAUL R.
> %PARISH,MICHAEL A.
>
> I'm thinking the only way to do this is with a if inside a loop but I
> didn't want to have to write the if as a if *elseif *elseif *for each
> special character. I was also hoping not to have to write Case logic
> for each special character either.
>
> Any ideas on the vba code to do this?
> are the numeric codes for speacial characters in a range? maybe
> there's some way to use a between 021 and 534. *oh, what a project
> this is..... *
> bobh.

Here's a function you might be able to modify.
Usage: ? StripVar("BITRAN+,DANI")
Public Function StripVar(var As Variant) As Variant

Dim intFor As Integer
Dim strHold As String
Dim intAsc As Integer
If Not IsNull(var) Then
var = Trim(var)
For intFor = 1 To Len(var)
strHold = Mid(var, intFor, 1)
intAsc = Asc(strHold)
'Spaces, commas, 0-9, A-Z, a-z are saved, rest discarded
If intAsc = 32 Or _ 'space
intAsc = 44 Or _ 'comma
(intAsc >= 48 And intAsc <= 57) Or _ '0-9
(intAsc >= 65 And intAsc <= 90) Or _ 'A-Z
(intAsc >= 97 And intAsc <= 122) Then 'a-z
StripVar = StripVar & strHold
End If
Next
End If

End Function

Le 11/06/2012 20:08, bobh a écrit :
> Hi All,
>
> first, to store data this way was not my idea, this is what I have to
> work with, which is, a extract from a mainframe system which I import
> into an Access2003 table. What I have to do is search for one fo these
> characters
>
> "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/","\","="
>
> in the value of the Full Name field and strip it off and then write
> the new value to the Full Name field in another table.

My two pences :

======================================

Function Spell_and_DropBadCaracters(Arg As String)
Dim GoodCaracters, Car, MyString As String
Dim i As Integer

GOOD_CARACTERS = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ"

Arg = Trim(Arg)

For i = 1 To Len(Arg)
Car = Mid(Arg, i, 1)
If InStr(GOOD_CARACTERS, Car) <> 0 Then
MyString = MyString & Car
End If
Next i

Debug.Print MyString

End Function

========================================

In execution windows type: Print spell_and_dropbadcaracters(" a,b+
,&,,,ak_C")

--
kd

bobh <vulcaned@yahoo.com> wrote in
news:b5923023-42b0-4f63-95aa-96776ac63e7a@n33g2000vbi.googlegroups.co
m:

> Hi All,
>
> first, to store data this way was not my idea, this is what I
> have to work with, which is, a extract from a mainframe system
> which I import into an Access2003 table. What I have to do is
> search for one fo these characters
>
> "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]"
> ,"?","/","\","="
>
> in the value of the Full Name field and strip it off and then
> write the new value to the Full Name field in another table.
>
> examples of imported name field
>
> BITRAN+,DANI
> BLUMENKOPF,BENNETT#
> CLARK=,PAUL R.
> %PARISH,MICHAEL A.
>
> I'm thinking the only way to do this is with a if inside a loop
> but I didn't want to have to write the if as a if elseif elseif
> for each special character. I was also hoping not to have to write
> Case logic for each special character either.
>
> Any ideas on the vba code to do this?
> are the numeric codes for speacial characters in a range? maybe
> there's some way to use a between 021 and 534. oh, what a project
> this is.....
> bobh.

Investigate the help file for the vba Select Case statement

It allows things like
Select Case stLetter2Test
Case "~","@", "#","$","%", "^","&","*","(",")","_"
' do nothing
Case "+","'","{","}","[","]" ,"?","/","\","="
' do something...
Case else
stFullName = stFullname & stLetter2Test
End select

--
Bob Q.
PA is y I've altered my address.

On Jun 11, 4:00*pm, Patrick Finucane <patrickfinucan...@gmail.com>
wrote:
> On Jun 11, 1:08*pm, bobh <vulca...@yahoo.com> wrote:
>
>
>
>
>
> > Hi All,
>
> > first, to store data this way was not my idea, *this is what I have to
> > work with, which is, a extract from a mainframe system which I import
> > into an Access2003 table. What I have to do is search for one fo these
> > characters
>
> > "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/"**,"\","="
>
> > in the value of the Full Name field and strip it off and then write
> > the new value to the Full Name field in another table.
>
> > examples of imported name field
>
> > BITRAN+,DANI
> > BLUMENKOPF,BENNETT#
> > CLARK=,PAUL R.
> > %PARISH,MICHAEL A.
>
> > I'm thinking the only way to do this is with a if inside a loop but I
> > didn't want to have to write the if as a if *elseif *elseif *for each
> > special character. I was also hoping not to have to write Case logic
> > for each special character either.
>
> > Any ideas on the vba code to do this?
> > are the numeric codes for speacial characters in a range? maybe
> > there's some way to use a between 021 and 534. *oh, what a project
> > this is..... *
> > bobh.
>
> Here's a function you might be able to modify.
> Usage: *? StripVar("BITRAN+,DANI")
> Public Function StripVar(var As Variant) As Variant
>
> * * Dim intFor As Integer
> * * Dim strHold As String
> * * Dim intAsc As Integer
> * * If Not IsNull(var) Then
> * * * * var = Trim(var)
> * * * * For intFor = 1 To Len(var)
> * * * * * * strHold = Mid(var, intFor, 1)
> * * * * * * intAsc = Asc(strHold)
> * * * * * * 'Spaces, commas, 0-9, A-Z, a-z are saved, rest discarded
> * * * * * * If intAsc = 32 Or _ * * *'space
> * * * * * * * * intAsc = 44 Or _ * *'comma
> * * * * * * * * (intAsc >= 48 And intAsc <= 57) Or _ * * *'0-9
> * * * * * * * * (intAsc >= 65 And intAsc <= 90) Or _ * * *'A-Z
> * * * * * * * * (intAsc >= 97 And intAsc <= 122) Then* 'a-z
> * * * * * * * * StripVar = StripVar & strHold
> * * * * * * End If
> * * * * Next
> * * End If
>
> End Function- Hide quoted text -
>
> - Show quoted text -

Thanks for this, with a couple of adjustments to fit my perticular
situation it works great
bobh.

On Jun 11, 4:21*pm, kduc <k...@huit.invalid> wrote:
> Le 11/06/2012 20:08, bobh a écrit :
>
> > Hi All,
>
> > first, to store data this way was not my idea, *this is what I have to
> > work with, which is, a extract from a mainframe system which I import
> > into an Access2003 table. What I have to do is search for one fo these
> > characters
>
> > "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/"*,"\","="
>
> > in the value of the Full Name field and strip it off and then write
> > the new value to the Full Name field in another table.
>
> My two pences :
>
> ======================================
>
> Function Spell_and_DropBadCaracters(Arg As String)
> Dim GoodCaracters, Car, MyString As String
> Dim i As Integer
>
> GOOD_CARACTERS = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ"
>
> * * Arg = Trim(Arg)
>
> * * For i = 1 To Len(Arg)
> * * * * Car = Mid(Arg, i, 1)
> * * * * If InStr(GOOD_CARACTERS, Car) <> 0 Then
> * * * * * * MyString = MyString & Car
> * * * * End If
> * * Next i
>
> * * Debug.Print MyString
>
> End Function
>
> ========================================
>
> In execution windows type: Print spell_and_dropbadcaracters(" a,b+
> ,&,,,ak_C")
>
> --
> kd

Thanks for this code, works good.

On Jun 11, 8:37*pm, Bob Quintal <rquin...@sPAmpatico.ca> wrote:
> bobh <vulca...@yahoo.com> wrote innews:b5923023-42b0-4f63-95aa-96776ac63e7a@n33g2000vbi.googlegroups.co
> m:
>
>
>
>
>
> > Hi All,
>
> > first, to store data this way was not my idea, *this is what I
> > have to work with, which is, a extract from a mainframe system
> > which I import into an Access2003 table. What I have to do is
> > search for one fo these characters
>
> > "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]"
> > ,"?","/","\","="
>
> > in the value of the Full Name field and strip it off and then
> > write the new value to the Full Name field in another table.
>
> > examples of imported name field
>
> > BITRAN+,DANI
> > BLUMENKOPF,BENNETT#
> > CLARK=,PAUL R.
> > %PARISH,MICHAEL A.
>
> > I'm thinking the only way to do this is with a if inside a loop
> > but I didn't want to have to write the if as a if *elseif *elseif
> > for each special character. I was also hoping not to have to write
> > Case logic for each special character either.
>
> > Any ideas on the vba code to do this?
> > are the numeric codes for speacial characters in a range? maybe
> > there's some way to use a between 021 and 534. *oh, what a project
> > this is..... *
> > bobh.
>
> Investigate the help file for the vba Select Case statement
>
> It allows things like
> Select Case stLetter2Test
> Case "~","@", "#","$","%", "^","&","*","(",")","_"
> * * * * ' do nothing
> Case "+","'","{","}","[","]" ,"?","/","\","="
> * * * * ' do something...
> Case else
> * * * * stFullName = stFullname & stLetter2Test
> End select
>
> --
> Bob Q.
> PA is y I've altered my address.- Hide quoted text -
>
> - Show quoted text -

Thanks.... I didn't think about stringing out the characters in the
Case logic..... one of those " I can't see the forest because the
trees are in the way"
bobh.

The following function takes two arguments, strText (to clean) and
strChars (to remove) and returns a string from which all unwanted
characters have been removed. The statement

NewFullName = StripChars(OldFullName, "~@#$%^&*()_+'{}[]?/\=")

should produce the desired results

Function StripChars(strText As String, strChars As String) As String
Dim strResult As String
Dim intCount As Integer

If (Len(strText) > 0) And (Len(strChars) > 0) Then
For intCount = 1 To Len(strText)
If InStr(strChars, Mid(strText, intCount, 1)) = 0 Then
strResult = strResult & Mid(strText, intCount, 1)
Next intCount
Else
strResult = strText
End If
StripChars = strResult
End Function


Hope this helps,


Jan T





On 11-Jun-12 20:08, bobh wrote:
> Hi All,
>
> first, to store data this way was not my idea, this is what I have to
> work with, which is, a extract from a mainframe system which I import
> into an Access2003 table. What I have to do is search for one fo these
> characters
>
> "~","@","#","$","%","^","&","*","(",")","_","+","' ","{","}","[","]","?","/","\","="
>
> in the value of the Full Name field and strip it off and then write
> the new value to the Full Name field in another table.
>
> examples of imported name field
>
> BITRAN+,DANI
> BLUMENKOPF,BENNETT#
> CLARK=,PAUL R.
> %PARISH,MICHAEL A.
>
> I'm thinking the only way to do this is with a if inside a loop but I
> didn't want to have to write the if as a if elseif elseif for each
> special character. I was also hoping not to have to write Case logic
> for each special character either.
>
> Any ideas on the vba code to do this?
> are the numeric codes for speacial characters in a range? maybe
> there's some way to use a between 021 and 534. oh, what a project
> this is.....
> bobh.