We help IT Professionals succeed at work.

MS Access How To Make Exports Recognize Returns or New Lines As Shown On Form

Dustin Stanley
on
140 Views
Last Modified: 2017-05-04
In my access form I have a description field. In that description field text box I have a description lets say for example we are talking about a yellow school bus toy....

Color: Yellow
Length: 19 Inches
Age: 5 Years to 7 Years


In the form and the Table it looks just like that. But when I export it looks like this:

Color: Yellow Length: 19 Inches Age: 5 Years to 7 Years

So how can get the exported field to look like what it shows me on the form?

Color: Yellow
Length: 19 Inches
Age: 5 Years to 7 Years


As in exporting I mean I am in VBA building a string such as:
Dim XmlString As String
Dim ConditionID As String
Dim ItemDescription As String
Dim rst1 As DAO.Recordset
Set rst1 = CurrentDb.OpenRecordset("AddFixedPriceItemTemp", dbOpenSnapshot)
ConditionID = rst1("ConditionID")
ItemDescription = rst1("SkuDescr")

XmlString =  "<ConditionID>" & ConditionID & "</ConditionID>" & vbNewLine 'ConditionID
    XmlString = XmlString & "<Country>US</Country>" & vbNewLine
    XmlString = XmlString & "<Currency>USD</Currency>" & vbNewLine
    XmlString = XmlString & "<Description>" & ItemDescription & "</Description>" & vbNewLine 'ItemDescription

Open in new window




Thanks for the help.
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
In a general sense, this data (Color, Length, Age) should all be in separate "Records"
Then this is ridiculously easy to do.
The syntax would be similar to what you are already using...

In your current (un-normailzed) design, ...you cannot easily:
Filter for a range of lengths
Get a sum (or average) of years
...etc
...In other words, ..you cannot aggregate any of this "String" data
You are also leaving yourself open to inconsistent data, because a user cant type in anything they want with no validation or restriction.
ex.:
Color: !qw34Bl
Length: g45 parsecs
Age: TBD

In your case, ...perhaps users are typing in a CR when they enter the data. (so that each segment is on a different line.)
The issue is that the CR character might not translate if you are building a string.

Here, since you are, ...in effect, ...entering one long "Description" string, ...it is not easy to determine where to break the line.
You would have to go through some machinations to break the line before the word that is in front of the ":"

Perhaps an expert can assist with that though...?

JeffCoachman
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
See if using vbCR works rather than vbNewLink.

You may actually need vbCRLF but embedding those characters in a text string will cause a record break and so that might cause a problem on the other end.
Dustin StanleyEntrepreneur

Author

Commented:
Ok as for the un-normalized database I can see what you are saying from the example. It wasn't a "Good" example but the description box can contain anything at all. Even a Story :) if need be.

Pat I thought about the vbnewline or  vbCRLF but I also thought that could get very tricky. I was and am still hoping someone might know a little secret for us.


As a side note. You would think Access would have an idea of where to put the new line. It clearly shows it in the form text box and the field value in the table.  I would think otherwise if when I closed the form or look at the table field later and it was all one long string.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
The problem isn't Access.  It is Excel.

I am sending this as an API through a Gzip Multipart MIME. I looked at my Gzip file and it shows the CRLF as in Notepad++ Here is a Snippet:

<Currency>USD</Currency>
<Description><![CDATA[<p>&#160;</p><div id="ds_div"><p style="text-align: center;"><strong><span style="color: #ff0010; font-size: x-large;"><em><span style="text-decoration: underline;">VIEW ITEM TITLE FOR CONDITION CODE &amp; MATCH BELOW FOR CONDITION</span></em></span></strong></p><p style="text-align: center;"><strong><span style="text-decoration: underline;"><span style="font-size: x-large;">100% SATISFACTION GUARANTEED!</span></span></strong></p><p style="text-align: center;" align="left"><strong><span style="text-decoration: underline;"><span style="font-size: x-large;">Duty Cycle: 1-ON-5-OFF
60Hz
115 VAC
1.6 Amps
Rated Thrust 700LBS
Cap Value 25-30 MFD

Open in new window



So Pat you might be on to something....It might be the Server interrupting it wrong....I wonder why?
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Most processes that read textfiles interpret CRLF as a new record.  Csv's might not if the string is delimited but I can't swear to it.  You might be able to substitute something like @@ and then convert that at the other end.
Dustin StanleyEntrepreneur

Author

Commented:
Thanks Pat for the help. This will lead me in the right direction. As I believe it is how the Server is interrupting it and then altering it. Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.