Prev: dynamically choose columns in query
Next: Format for text field
From: ftrujill0 on 14 Apr 2010 12:54
I have an imported flat data file that has extended ASCII characters (i.e.
189, 239 and 191) in a Customer Name field. The extended characters are not
always in the same location of the Customer Name field and are not always the
same extended ASCII characters.

My question is there a simple way to query for extended ASCII character
(>127) in Access to identify such records and then clean them out of the name
record?

Example:

FName
=====
Joe�
B�ill
etc...


THANKS IN ADVANCE!!
From: KARL DEWEY on 14 Apr 2010 14:07
You can use a Replace function.
Replace([Customer Name], "┐", "")

The replace function can be nested as you would IIFs. If you have a whole
lot or they are unknown the you can use a table, [Extended].[X_Char], that is
not joined in your query (will run slow) like this --
Replace([Customer Name], [Extended].[X_Char], "")

If you do not know the characters then fill the table with the expected
Ascii values and then this --
Replace([Customer Name], Chr([Extended].[X_Char]), "")

--
Build a little, test a little.


"ftrujill0" wrote:

> I have an imported flat data file that has extended ASCII characters (i.e.
> 189, 239 and 191) in a Customer Name field. The extended characters are not
> always in the same location of the Customer Name field and are not always the
> same extended ASCII characters.
>
> My question is there a simple way to query for extended ASCII character
> (>127) in Access to identify such records and then clean them out of the name
> record?
>
> Example:
>
> FName
> =====
> Joe�
> B�ill
> etc...
>
>
> THANKS IN ADVANCE!!
 | 
Pages: 1
Prev: dynamically choose columns in query
Next: Format for text field