By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

ASCii Character Removal Pains

P: 1
I sure hope there is a Guru out there that can help me with this!
I have a database where I import tables with customer names and address as 2 of the imported fields. These fields need to be 'cleaned' before I can sub them into other queries for my final analysis. Our groups censentual assent was to remove all ASCii characters (Since we have to run the data through an address standardization program anyway).

I can create a query to remove them...but there are 2 problems with this.
1) The number of fields for a 'replace' field query would be 42 fields, and very time I run the select query, it hands on me and forces a shutdown.
2) I can create multiple queries feeding off of preceeding queries...but I'd have to create quite a few of them, and I already have a query matrix of more than 29 queries of various types.

Ideally, I know that you can do a VBA module for this kind of thing. However, although I am proficent with every other aspect of Access, I know nothing about VBA, how to set it up, run it, or code it.

The characters that I am trying to eliminate from the customer & address fields are as follows:
.
,
/
'
;
<
>
?
:
"
[
]
{
}
\
|
`
~
!
@
#
$
%
^
&
*
(
)
_
-
=
+


(The #, * and " signs are tricky, as in Access they are exclusive to wildcards, numbers and characters. But I have found a way to get around that.)
If anyone can help me on this, it would be greatly appreciated!
Thanks
May 11 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,638
I sure hope there is a Guru out there that can help me with this!
I have a database where I import tables with customer names and address as 2 of the imported fields. These fields need to be 'cleaned' before I can sub them into other queries for my final analysis. Our groups censentual assent was to remove all ASCii characters (Since we have to run the data through an address standardization program anyway).

I can create a query to remove them...but there are 2 problems with this.
1) The number of fields for a 'replace' field query would be 42 fields, and very time I run the select query, it hands on me and forces a shutdown.
2) I can create multiple queries feeding off of preceeding queries...but I'd have to create quite a few of them, and I already have a query matrix of more than 29 queries of various types.

Ideally, I know that you can do a VBA module for this kind of thing. However, although I am proficent with every other aspect of Access, I know nothing about VBA, how to set it up, run it, or code it.

The characters that I am trying to eliminate from the customer & address fields are as follows:
.
,
/
'
;
<
>
?
:
"
[
]
{
}
\
|
`
~
!
@
#
$
%
^
&
*
(
)
_
-
=
+


(The #, * and " signs are tricky, as in Access they are exclusive to wildcards, numbers and characters. But I have found a way to get around that.)
If anyone can help me on this, it would be greatly appreciated!
Thanks
The following code will 'Scrub' the [Address] Field of tblEmployee eliminating the ASCII characters which you had specified. It checks every single character in every [Address] Field for what you specified as 'Elimination Characters'. If the code finds one of these characters, it does not append it to the dynamically rebuilt String ([Address]). Any questions, fell free to ask. There is probably a simpler Method, but it does not dawn on me at the moment, and I was pressed for time:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intCharCounter As Integer, intLenOfField As Integer
  3. Dim strNewString As String
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("tblEmployee", dbOpenDynaset)
  7.  
  8. MyRS.MoveFirst
  9.  
  10. Do While Not MyRS.EOF
  11.   If Not IsNull(MyRS![Address]) Then
  12.     intLenOfField = Len(MyRS![Address])
  13.       For intCharCounter = 1 To intLenOfField
  14.         'If next line is True then the String is clean
  15.         If InStr(",/';<>?:[]{}\|~!@#$%^&*()-_=+", Mid$(MyRS![Address], intCharCounter, 1)) = 0 Then
  16.           strNewString = strNewString & Mid$(MyRS![Address], intCharCounter, 1)
  17.         End If
  18.       Next
  19.       MyRS.Edit
  20.         MyRS![Address] = strNewString
  21.       MyRS.Update
  22.         strNewString = vbNullString
  23.         MyRS.MoveNext
  24.   Else
  25.     MyRS.MoveNext
  26.   End If
  27. Loop
  28.  
  29. MyRS.Close
May 12 '07 #2

JConsulting
Expert 100+
P: 603
I sure hope there is a Guru out there that can help me with this!
I have a database where I import tables with customer names and address as 2 of the imported fields. These fields need to be 'cleaned' before I can sub them into other queries for my final analysis. Our groups censentual assent was to remove all ASCii characters (Since we have to run the data through an address standardization program anyway).

I can create a query to remove them...but there are 2 problems with this.
1) The number of fields for a 'replace' field query would be 42 fields, and very time I run the select query, it hands on me and forces a shutdown.
2) I can create multiple queries feeding off of preceeding queries...but I'd have to create quite a few of them, and I already have a query matrix of more than 29 queries of various types.

Ideally, I know that you can do a VBA module for this kind of thing. However, although I am proficent with every other aspect of Access, I know nothing about VBA, how to set it up, run it, or code it.

The characters that I am trying to eliminate from the customer & address fields are as follows:
.
,
/
'
;
<
>
?
:
"
[
]
{
}
\
|
`
~
!
@
#
$
%
^
&
*
(
)
_
-
=
+


(The #, * and " signs are tricky, as in Access they are exclusive to wildcards, numbers and characters. But I have found a way to get around that.)
If anyone can help me on this, it would be greatly appreciated!
Thanks

Here's an alternative as well. It uses the ascii equivalent for a character

Expand|Select|Wrap|Line Numbers
  1. This function should remove any characters out of the normal range (from space to tilde):
  2. Function fixstr(str As String) As String
  3. Dim c As String
  4. Dim strnew As String
  5. For i = 1 To Len(str)
  6.   c = Mid(str, i, 1)
  7.   Debug.Print Asc(c)
  8.   If Asc(c) > 31 And Asc(c) < 127 Then
  9.      strnew = strnew & c
  10.   End If
  11. Next
  12. fixstr = strnew
  13. End Function
  14.  
Call it like this:

Someting = Fixstr(YourString)

for reference This is a link to an ASCII Chart:
http://www.lookuptables.com/
J
May 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.