special characters and new line

[Dave]

I am having a problem after loading an Access 2002 DB with
data from an XML file. Once loaded, one of the text fields
that contains descriptive data (up to 3000 characters)
does not represent the new line characters. Therefore all
the text is bunched together with the special character
square in the place where the new lines should be.

I am loading the db via a Java program and have
appended "\r\n", newline(), and other variations but to no
avail. The XML file looks fine in any of the editors that
I view it in. I cannot figure out why Access does not
recognize the sequence from the file. I have heard that
it must be chr$(13) and chr$(10) for Access relating to
the new line, but that is what is in the XML file.

Does anyone know a way to determine what the special
control character are in Access that are represented by
the small square special symbols? Also, once identified,
is there a way to use the replace command with special
characters for all the records? I tried copying the
special character from the field with the intention of
putting it in the replace dialog but it won't let me past
it.

I'm not sure what else to try and I have loaded a couple
of thousand records, so it can not be fixed manually.

Does Access handle a new line differently than most other
programs? If it must be a chr$(13) and a chr$(10) isn't
this just a standard CR/LF? Is Access looking for
specific ascii values besides those that represent the
ascii cr/lf (Dec 10 and 13)? I'd really like to solve the
problem via the loader, but I cannot figure out the ascii
sequence that translates into Access correctly.

Thanks,

Dave

 

[Marshall Barton]

I am having a problem after loading an Access 2002 DB with
data from an XML file. Once loaded, one of the text fields
that contains descriptive data (up to 3000 characters)
does not represent the new line characters. Therefore all
the text is bunched together with the special character
square in the place where the new lines should be.

I am loading the db via a Java program and have
appended "\r\n", newline(), and other variations but to no
avail. The XML file looks fine in any of the editors that
I view it in. I cannot figure out why Access does not
recognize the sequence from the file. I have heard that
it must be chr$(13) and chr$(10) for Access relating to
the new line, but that is what is in the XML file.

Many programs use a different character sequence to
represent a new line. I seem to remember that Excel uses LF
- Chr(10) while Access uses CRLF - Chr(13) & Chr(10). Other
programs use other sequences - it's all a big pain in the
you know what :-(

Does anyone know a way to determine what the special
control character are in Access that are represented by
the small square special symbols? Also, once identified,
is there a way to use the replace command with special
characters for all the records? I tried copying the
special character from the field with the intention of
putting it in the replace dialog but it won't let me past
it.

There are probably many ways to this, a hex viewer is the
most obvious. Since I have almost no other use for one of
those, I usually just write a little procedure in an Access
module to display any special chars in a string.

Public Sub ShowCtrlChars(str As String)
Dim intPos As Integer
Dim intCharCode As Integer

For intPos = 1 to Len(str)
intCharCode = Asc(Mid(str, intPos, 1))
If intCharCode < 32 Then
Debug.Print intPos, intCharCode
End If
Next intPos
End Sub

Run the procedure from the debug window to see the control
chars and their position in the string.

I'm not sure what else to try and I have loaded a couple
of thousand records, so it can not be fixed manually.

Does Access handle a new line differently than most other
programs? If it must be a chr$(13) and a chr$(10) isn't
this just a standard CR/LF?

Yes, Access uses the usual DOS new line sequence, but not
all that many other programs actually follow that lead.

Is Access looking for
specific ascii values besides those that represent the
ascii cr/lf (Dec 10 and 13)? I'd really like to solve the
problem via the loader, but I cannot figure out the ascii
sequence that translates into Access correctly.

Once you figure out what the Java thingy is doing with new
lines, then you can translate that into the CrLf sequence
that Access requires by using the Replace function. You can
even do it in an Update query if you really want to change
the data stored in your Access tables, but I would first try
converting it on the fly only when I need to display it.

Just changing a text box's Control Source from the name of
the field to an expression such as
=Replace([thefield], Chr(10??), Chr(13) & Chr(10))

(NOTE: Make sure that the text box has a different name than
the field referred to in the expression.)

 

[Dave]

Thanks Marshall,

I try what you suggested and let you know how it goes!

Dave

-----Original Message-----

I am having a problem after loading an Access 2002 DB with
data from an XML file. Once loaded, one of the text fields
that contains descriptive data (up to 3000 characters)
does not represent the new line characters. Therefore all
the text is bunched together with the special character
square in the place where the new lines should be.

I am loading the db via a Java program and have
appended "\r\n", newline(), and other variations but to no
avail. The XML file looks fine in any of the editors that
I view it in. I cannot figure out why Access does not
recognize the sequence from the file. I have heard that
it must be chr$(13) and chr$(10) for Access relating to
the new line, but that is what is in the XML file.

Many programs use a different character sequence to
represent a new line. I seem to remember that Excel uses LF
- Chr(10) while Access uses CRLF - Chr(13) & Chr(10). Other
programs use other sequences - it's all a big pain in the
you know what :-(

Does anyone know a way to determine what the special
control character are in Access that are represented by
the small square special symbols? Also, once identified,
is there a way to use the replace command with special
characters for all the records? I tried copying the
special character from the field with the intention of
putting it in the replace dialog but it won't let me past
it.

There are probably many ways to this, a hex viewer is the
most obvious. Since I have almost no other use for one of
those, I usually just write a little procedure in an Access
module to display any special chars in a string.

Public Sub ShowCtrlChars(str As String)
Dim intPos As Integer
Dim intCharCode As Integer

For intPos = 1 to Len(str)
intCharCode = Asc(Mid(str, intPos, 1))
If intCharCode < 32 Then
Debug.Print intPos, intCharCode
End If
Next intPos
End Sub

Run the procedure from the debug window to see the control
chars and their position in the string.

I'm not sure what else to try and I have loaded a couple
of thousand records, so it can not be fixed manually.

Does Access handle a new line differently than most other
programs? If it must be a chr$(13) and a chr$(10) isn't
this just a standard CR/LF?

Yes, Access uses the usual DOS new line sequence, but not
all that many other programs actually follow that lead.

Is Access looking for
specific ascii values besides those that represent the
ascii cr/lf (Dec 10 and 13)? I'd really like to solve the
problem via the loader, but I cannot figure out the ascii
sequence that translates into Access correctly.

Once you figure out what the Java thingy is doing with new
lines, then you can translate that into the CrLf sequence
that Access requires by using the Replace function. You can
even do it in an Update query if you really want to change
the data stored in your Access tables, but I would first try
converting it on the fly only when I need to display it.

Just changing a text box's Control Source from the name of
the field to an expression such as
=Replace([thefield], Chr(10??), Chr(13) & Chr(10))

(NOTE: Make sure that the text box has a different name than
the field referred to in the expression.)