Delete 'mystery' character from data (possibly carriage return)

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,

I've imported an XML file into a new access database and have a number of
tables of information. I have a bit of problem with the data that's been
imported though - where it's present it's always preceded by a ï¯. I can't
select the character. If in one table I've got a lot of dates where ![Year] =
1972, I can't filter the results of a query based on the table by putting
"1972" in the criteria of the query - I get 0 rows.

I think that the ï¯ character represents a carriage return, although I may be
wrong. Does anyone know how I can delete it from my data?

Any help much appreciated as always!
 
There's a bunch of strange characters that can show up as the little square.
So the first thing to do is find out which one. Use a combination of the Asc
and maybe the Mid functions to find out the Ascii code. It's the first
character, just the ASC function will work. Try this in a query:

TheCode: Asc([Year])

Sort on this field. Most of the strange codes will be below 30.

Then look up the returned numbers in an ASCII chart using Bing or Google.
That will tell you the codes. Here's one:
http://msdn.microsoft.com/en-us/library/4z4t9ed1(VS.80).aspx

Next use the Replace function to get ride of them:

ReplaceIt: Replace([SpecialCharactersText]," ","")

The trick is to put the special character code between the second set of
quotation marks. For the code 160 you would do this:

Hold down the Alt key and type in 0160 using the keypad. Make sure to put a
" before then after the Alt+0160.

A couple of warnings. First make a copy of that table just in case
something goes wrong. Also the "" is a zero length string. If you have any
of the squares in a field that has no other data, it might cause problems if
you are looking for Nulls because a ZLS is not a null.
 
Brilliant! Many thanks for your help, I've now been able to write a procedure
to whizz through each table in the db and get rid of the characters making it
useable :-)


--
Regards,

Steve


Jerry Whittle said:
There's a bunch of strange characters that can show up as the little square.
So the first thing to do is find out which one. Use a combination of the Asc
and maybe the Mid functions to find out the Ascii code. It's the first
character, just the ASC function will work. Try this in a query:

TheCode: Asc([Year])

Sort on this field. Most of the strange codes will be below 30.

Then look up the returned numbers in an ASCII chart using Bing or Google.
That will tell you the codes. Here's one:
http://msdn.microsoft.com/en-us/library/4z4t9ed1(VS.80).aspx

Next use the Replace function to get ride of them:

ReplaceIt: Replace([SpecialCharactersText]," ","")

The trick is to put the special character code between the second set of
quotation marks. For the code 160 you would do this:

Hold down the Alt key and type in 0160 using the keypad. Make sure to put a
" before then after the Alt+0160.

A couple of warnings. First make a copy of that table just in case
something goes wrong. Also the "" is a zero length string. If you have any
of the squares in a field that has no other data, it might cause problems if
you are looking for Nulls because a ZLS is not a null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve said:
Hi,

I've imported an XML file into a new access database and have a number of
tables of information. I have a bit of problem with the data that's been
imported though - where it's present it's always preceded by a ï¯. I can't
select the character. If in one table I've got a lot of dates where ![Year] =
1972, I can't filter the results of a query based on the table by putting
"1972" in the criteria of the query - I get 0 rows.

I think that the ï¯ character represents a carriage return, although I may be
wrong. Does anyone know how I can delete it from my data?

Any help much appreciated as always!
Click to expand...
Click to expand...
 
Back
Top