Access VBA query to remove special chars - desperate 1

[ordendelfai]

Hi Everyone,

I cannot find a way to do this in SQL, and am very new to VBA. I have been trying to figure this out, but am almost out of time, if you can help really would appreciate .

I have a table (say "tbl_Data) that has thousands of records, with one field ("GroupName&quot that holds string data. I am trying to compare that one field to another field with SQL, but the problem is the data in tbl_Data has characters that mess up the match in the other table.

The characters are:
, (comma)
/ (forward slash)
- (dash)
. (Period)
" " (space)
# (Hash)
( (Left Parenthesis)
) (Right Parenthesis)

I need some sort of vba function that tests the data in the GroupName field for each record in this table for these characters, and removes them (loops for each record) For example:

Before:
THIS GROUP HAS THESE/CHARACTERS-NEED TO REMOVE

After:
THISGROUPHASTHESECHARACTERSNEEDTOREMOVE

Thanks!

Joel

 

[JPMontreal]

Hi,

Here are the codes that will do what you like. I didn’t fully test it, so make a copy of your table or database before your try it. Copy this code in a module of your database and make sure, before you use it that you have a reference, in Tool >Reference, to “Microsoft ActiveX Data Object 2.7”.

Option Compare Database

Public Sub RemoveChar()
'By Jean-Paul Viel
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim strQuery As String
Dim strName As String
Dim strEnd As String
Dim intN As Integer
Dim Flag As Boolean

Set Conn = CurrentProject.Connection


strQuery = "SELECT GroupName FROM test" 'tbl_Data"

Set Rs = New ADODB.Recordset
With Rs
Set .ActiveConnection = Conn
.CursorType = adOpenDynamic
.Source = strQuery
.LockType = adLockOptimistic
.Open
End With

Rs.MoveFirst
Do Until Rs.EOF
strName = Rs.Fields("GroupName&quot.Value
For intN = 1 To Len(strName)
Flag = True
Select Case Mid(strName, intN, 1)
Case ","
Flag = False
Case "/"
Flag = False
Case "-"
Flag = False
Case " "
Flag = False
Case "#"
Flag = False
Case "("
Flag = False
Case &quot"
Flag = False
End Select
If Flag = True Then strEnd = strEnd + Mid(strName, intN, 1)
Next
If Len(strName) <> Len(strEnd) Then
If Len(strEnd) = 0 Then strEnd = &quot; &quot;
Rs.Fields(&quot;groupName&quot.Value = strEnd
Rs.Update
End If
strEnd = &quot;&quot;
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing

End Sub




Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[ordendelfai]

If I could kiss you I would! But since I can't (and wouldn't really...), here is a star. That worked perfectly! Don't understand but a bit of it, but it really does work! =D

Thanks again!,

Joel

 

[JPMontreal]

Hi Joel,

Thanks for the star, I would like rather some reference then a kiss. Could you send me a note at the address below.




Jean-Paul
Montreal
jp@solutionsvba.com

 

[ordendelfai]

I shall do that this week! ;0)

Ran into one problem at work though. I use Office XP at home, but my employer still is on Access 97. Is there anyway to make a working version of this code for 97? If it would be a lot of trouble, don't worry, I can do this at home, but if it would be easy enough would really appreciate

Thanks,

Joel

 

[JPMontreal]

Hi,

Here is the modification:

Make sure that you have in Tool > reference…
Microsoft DAO 3.6 object library

Public Sub DAORomeveChar()
'by Jean-Paul Viel
Dim db As Database
Dim rs As Recordset
Dim strQuery As String
Dim strName As String
Dim strEnd As String
Dim intN As Integer
Dim Flag As Boolean

strQuery = &quot;SELECT GroupName FROM tbl_Data&quot;

Set db = CurrentDb()
Set rs = db.OpenRecordset(strQuery)

rs.MoveFirst
Do Until rs.EOF
strName = rs.Fields(&quot;GroupName&quot.Value
For intN = 1 To Len(strName)
Flag = True
Select Case Mid(strName, intN, 1)
Case &quot;,&quot;
Flag = False
Case &quot;/&quot;
Flag = False
Case &quot;-&quot;
Flag = False
Case &quot; &quot;
Flag = False
Case &quot;#&quot;
Flag = False
Case &quot;(&quot;
Flag = False
Case &quot&quot;
Flag = False
End Select
If Flag = True Then strEnd = strEnd + Mid(strName, intN, 1)
Next
If Len(strName) <> Len(strEnd) Then
If Len(strEnd) = 0 Then strEnd = &quot; &quot;
rs.Edit
rs.Fields(&quot;groupName&quot.Value = strEnd
rs.Update
End If
strEnd = &quot;&quot;
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
db.Close

End Sub

.



Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com