Search and Replace -- non-ASCII charaters

[EHPorter]

I am in the process of switching from the Paradox database to Access. When
I import Paradox memo fields into Access, the hard returns (CR-LF) contained
in the original paradox file are replaced by a small square box in Access.
Does anyone know of a way to search for and replace these small square box
characters with hard returns?

 

[Ken Snell]

CR is Chr(13) in ASCII code. LF is Chr(10) in ASCII code.

I'm not familiar with Paradox, but if you're seeing a box instead of a
carriage return and/or line feed, then the Paradox file likely contains only
one or the other of the two fields. ACCESS uses them in combination in order
to make a new line in a field.

You might import the data and then test the field (in a query) to see which
of the two characters are actually in the field, and then run an update
query that converts that one character to the combination (Chr(13) &
Chr(10)) for ACCESS to properly display the separate lines.

 

[EHPorter]

Thank you. This at least gets me started. Remember, however, that I am a
complete newbie in Access, although years of Paradox have at least given me
some idea of how database theory works. The question I don't understand is
this: How do I get Access to search/query for a particular ASCII
character?

I did try to highlight the "small square box" character, and paste it into
the "find and replace" box. No luck.

Thanks.

 

[Ken Snell]

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get you
the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's the
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

 

[EHPorter]

No luck so far. The "box" does not appear to be either LF or CR once
imported into Access. Searching for either Chr(10) or Chr(13) does not
produce a hit on those records containing the box.

The Windows XP Character Map shows this character as "Small White Box"
Character U+25A1 in both the Times Roman and Arial character sets. Think
that's what it is? And if so, any idea how to search and replace for this?
I've tried your SQL Query formula; it works with Chr(10) but attempting to
search for character Chr(25A1) or Chr(&H25A1) produces a syntax error.

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get you
the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's the
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however, that I am a
complete newbie in Access, although years of Paradox have at least given me
some idea of how database theory works. The question I don't understand is
this: How do I get Access to search/query for a particular ASCII
character?

I did try to highlight the "small square box" character, and paste it into
the "find and replace" box. No luck.

Thanks.


contains
only square
box

 

[Ken Snell]

The "box" will be displayed by ACCESS for many characters besides just
Chr(13) and Chr(10).

If you know the "location" of that box in the string (it's the 13th
character, for example), you could do a loop through the ASCII character set
to see if you get a match (I'm using TextString as a generic placeholder for
the actual text string from the field):

Dim IntChar As Integer
For IntChar = 0 To 255
If Mid("TextString", 13, 1) = Chr(intChar) Then
MsgBox "The character is Chr(" & intChar & ")"
Exit For
End If
Next intChar

I don't believe that the Chr function accepts any argument other than the
Long Integer or Integer value between 0 and 255.

--
Ken Snell
<MS ACCESS MVP>

No luck so far. The "box" does not appear to be either LF or CR once
imported into Access. Searching for either Chr(10) or Chr(13) does not
produce a hit on those records containing the box.

The Windows XP Character Map shows this character as "Small White Box"
Character U+25A1 in both the Times Roman and Arial character sets. Think
that's what it is? And if so, any idea how to search and replace for this?
I've tried your SQL Query formula; it works with Chr(10) but attempting to
search for character Chr(25A1) or Chr(&H25A1) produces a syntax error.

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get you
the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's th e
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however, that I

am

a given
me understand
is

 

[Douglas J. Steele]

Um, Ken, if you know it's the 13th character, you can simply go
Asc(Mid("TextString", 13, 1)) to find out what it is!

What I often do under these circumstances is loop through the string,
printing it out character by character:

Dim lngLoop As Long

For lngLoop = 1 To Len(strText)
Debug.Print Asc(Mid(strText, lngLoop, 1)) & " (" & Mid(strText,
lngLoop, 1) & ")"
Next lngLoop

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

The "box" will be displayed by ACCESS for many characters besides just
Chr(13) and Chr(10).

If you know the "location" of that box in the string (it's the 13th
character, for example), you could do a loop through the ASCII character set
to see if you get a match (I'm using TextString as a generic placeholder for
the actual text string from the field):

Dim IntChar As Integer
For IntChar = 0 To 255
If Mid("TextString", 13, 1) = Chr(intChar) Then
MsgBox "The character is Chr(" & intChar & ")"
Exit For
End If
Next intChar

I don't believe that the Chr function accepts any argument other than the
Long Integer or Integer value between 0 and 255.

--
Ken Snell
<MS ACCESS MVP>

No luck so far. The "box" does not appear to be either LF or CR once
imported into Access. Searching for either Chr(10) or Chr(13) does not
produce a hit on those records containing the box.

The Windows XP Character Map shows this character as "Small White Box"
Character U+25A1 in both the Times Roman and Arial character sets. Think
that's what it is? And if so, any idea how to search and replace for this?
I've tried your SQL Query formula; it works with Chr(10) but attempting to
search for character Chr(25A1) or Chr(&H25A1) produces a syntax error.

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will

get
you

the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's

th

e

"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however, that

I

am it
into

of

a

combination

in

 

[Ken Snell]

So true....I get into such a mindset with using Chr and forget about Asc
function!

< slap to head > "now remember this" he says to himself.....

< g >


--
Ken Snell
<MS ACCESS MVP>

Um, Ken, if you know it's the 13th character, you can simply go
Asc(Mid("TextString", 13, 1)) to find out what it is!

What I often do under these circumstances is loop through the string,
printing it out character by character:

Dim lngLoop As Long

For lngLoop = 1 To Len(strText)
Debug.Print Asc(Mid(strText, lngLoop, 1)) & " (" & Mid(strText,
lngLoop, 1) & ")"
Next lngLoop

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

The "box" will be displayed by ACCESS for many characters besides just
Chr(13) and Chr(10).

If you know the "location" of that box in the string (it's the 13th
character, for example), you could do a loop through the ASCII character set
to see if you get a match (I'm using TextString as a generic placeholder for
the actual text string from the field):

Dim IntChar As Integer
For IntChar = 0 To 255
If Mid("TextString", 13, 1) = Chr(intChar) Then
MsgBox "The character is Chr(" & intChar & ")"
Exit For
End If
Next intChar

I don't believe that the Chr function accepts any argument other than the
Long Integer or Integer value between 0 and 255.

attempting

to

search for character Chr(25A1) or Chr(&H25A1) produces a syntax error.



Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get
you
the basic framework (change the name TableName to the real table

name,
and

change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If

that's

th

e
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it
again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage

Return
and

Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however,

that

I

instead

of combination to
see

(Chr(13)

&

box

in

 

[EHPorter]

It's been a while since I posted my query, but I finally got things figured
out. I just wanted to thank you for your input.

Paradox indeed uses ASCII 10 (Hex 0a) as it's hard return character. I
finally looked with a hex editor. Your substitution string worked
perfectly. The reason I could not get it to work previously was
inexperience with Access and a lot of stupid mistakes. Once I determined
with the hex editor that the character in question was indeed Chr(10), I
kept working with your code until I figured out what mistakes I was making
that kept it from running properly.

Thanks again.

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get you
the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's the
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however, that I am a
complete newbie in Access, although years of Paradox have at least given me
some idea of how database theory works. The question I don't understand is
this: How do I get Access to search/query for a particular ASCII
character?

I did try to highlight the "small square box" character, and paste it into
the "find and replace" box. No luck.

Thanks.


contains
only square
box

 

[Ken Snell]

Glad to hear your persistence paid off! Good luck.

--
Ken Snell
<MS ACCESS MVP>

It's been a while since I posted my query, but I finally got things figured
out. I just wanted to thank you for your input.

Paradox indeed uses ASCII 10 (Hex 0a) as it's hard return character. I
finally looked with a hex editor. Your substitution string worked
perfectly. The reason I could not get it to work previously was
inexperience with Access and a lot of stupid mistakes. Once I determined
with the hex editor that the character in question was indeed Chr(10), I
kept working with your code until I figured out what mistakes I was making
that kept it from running properly.

Thanks again.

Set up a query that uses the table with the data as the source table (I'll
call it TableName in my example below). The SQL statement below will get you
the basic framework (change the name TableName to the real table name, and
change FieldName to the name of the field that contains the text strings):

SELECT * FROM TableName
WHERE InStr([FieldName], Chr(13)) > 0;

This will search for the line feed character in the string. If that's the
"box", then all records with it in that string will be returned.

If no records are returned, then change Chr(13) to Chr(10) and try it again.

Let's assume that you find that the box is the Chr(10) (carriage return)
character. You can replace it with the combination of Carriage Return and
Line Feed by using an update query that will change the character to the
combination:

UPDATE TableName
SET [FieldName] = Replace([FieldName], Chr(10), Chr(13) & Chr(10), 1, -1,
1);


Hope that gives you an extra "boost" ! < g >

--
Ken Snell
<MS ACCESS MVP>

Thank you. This at least gets me started. Remember, however, that I

am

a given
me understand
is