473,320 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ASCii Character Removal Pains

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
2 6304
ADezii
8,834 Expert 8TB
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
603 Expert 512MB
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

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

Similar topics

5
by: Daniel | last post by:
Hi, is there a way to check if a letter entered is an uppercase ASCII character? Thanks Daniel
19
by: Ian | last post by:
I'm using the following meta tag with my documents: <meta http-equiv="Content-Type" content= "text/html; charset=us-ascii" /> and yet using character entities like &rsquo; and &mdash; It...
37
by: chandy | last post by:
Hi, I have an Html document that declares that it uses the utf-8 character set. As this document is editable via a web interface I need to make sure than high-ascii characters that may be...
11
by: Kai Bohli | last post by:
Hi all ! I need to translate a string to Ascii and return a string again. The code below dosen't work for Ascii (Superset) codes above 127. Any help are greatly appreciated. protected...
9
by: simchajoy2000 | last post by:
Hi, I know what the ASCII Character Codes are for the 2nd and 3rd powers in VB.NET but I can't find the 6th power anywhere - does anyone know what it might be or if it even exists? Joy
399
by: =?UTF-8?B?Ik1hcnRpbiB2LiBMw7Z3aXMi?= | last post by:
PEP 1 specifies that PEP authors need to collect feedback from the community. As the author of PEP 3131, I'd like to encourage comments to the PEP included below, either here (comp.lang.python), or...
4
by: meendar | last post by:
Hi, I am having a character pointer which contains ascii values. i just want to convert all these ascii values to respective characters and again store it in another character pointer. ...
5
by: tushar.saxena | last post by:
This post is a follow up to the post at : http://groups.google.com/group/comp.lang.c++/browse_thread/thread/83af6123fa945e8b?hl=ug#9eaa6fab5622424e as my original question was answered there, but I...
9
by: =?Utf-8?B?RGFu?= | last post by:
I have the following code section that I thought would strip out all the non-ascii characters from a string after decoding it. Unfortunately the non-ascii characters are still in the string....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.