Carriage Returns stripped out or are different in linked DB

[tstoneami]

I saw other posts which mentioned that CR's are represented differently in other systems (ie Mysql) and have to be converted.

I am attempting to find out how they are implemented in Quickbase, because when I pull the data into the report, they are not respected and all the formatting is lost with respect to CR's.

What is involved in converting them in the event I can find out how they are represented in the source table? Is there a way to determine this after the data is in Access?

Thanks!



UPDATE: Someone in the QB community stated that CR's in there are ascii 13, but I don't know if that helps this question...

[June7]

If Access does not understand character might substitute with something else. If you see odd symbol in data, that is probably where the Quickbase CR's were. Figure out the ASCII code for that character and replace it with Access CrLf. Something like:

Repace([fieldname], Chr(some number for the bad character), Chr(13) & Chr(10))

[tstoneami]

Yeah, there is nothing there. Just a blank spot. Dunno what to do just yet.

[June7]

A blank spot as in a space? Try this test:

1. select that 'blank' and copy it to clipboard (Ctrl+C)

2. go to the VBA immediate window and type

?Asc("_")

but in place of the underscore paste from the clipboard (Ctrl+V)

3. with cursor in position after the second paren, press Enter

4. what is the value returned, if any
32 is code for space

[tstoneami]

Yup Its 32...but, I am actually cutting it from the report on the screen...where should I be cutting it from?

[June7]

From the data in the table. Should get the same result.

[tstoneami]

Yup Its 32...I don't really understand why it's stripping it out. At this point the only thing I can think of is to have them put in a character I can parse. What is happening I wonder.

[June7]

If Access is not substituting an unrecognized character then it is dropping because that's all it can do.

I presume there are valid spaces in the string so can't parse (or replace) on space.

Are the CR's really vital for displaying this data?

[tstoneami]

Yes, the notes and legal description fields which I put on the report are very structured in the system. The intent was for them to be represented that way in the report. If I open a table and look at the data in the field, there isn't even a space. In the report, there is a space where the CR goes, but in the table there is nothing. I am somewhat desparate, as I did all my report layout and just started printing reports for review today. I am worried I will have to switch to something else, or, tell the operators in insert a parsable character. My employer is not happy. UGH.

To clarify, the auditors have bulletted sections, etc. Now its just one long chunk. Looks really bad.

[June7]

If there was nothing in the table field, how did you get the ASCII code 32 in the immediate window? If the report shows a space then there must be SOMETHING in the data in that position.

I've never heard of Quickbase. You might find this of interest https://quickbase-community.intuit.c...yY?legacy=true

I just read your update in first post about the CR code 13.

Maybe the issue is need the LF as well. Try:

Replace([fieldname], Chr(13), Chr(13) & Chr(10))

How are you getting the data from Quickbooks to Access? Quickbooks exports to a CSV or Excel file then you import that file?

View the file in Excel. Do you see odd characters?

[tstoneami]

I'm getting the data to Access via Qunect - an ODBC connector for QB. I can tell you that when I open the field in Access by opening the table there is literally nothing AT ALL in that position that is highlightable.

I thought about exporting to excel while I was driving home - good call. I like your replace idea as well. I will be feeding back after I get a chance to try this.

Thank you for your interest in helping out. Also I referenced that QB info up in my second post - at the time I didn't have any context tho.

[June7]

Use the Replace function on that field in query or report textbox. If it works we know that the CR code is in the data and the report is translating it so appears to be a space. Access needs the LF code as well to actually force a new line in the textbox.

[tstoneami]

UPDATE: I tested the replace method above - it did nothing. I ran the report in report view, cut the section out of the textbox and pasted it in Word, hoping that I could somehow expose the characters in there somehow. To my surprise, all the carriage returns were respected and the output was formatted properly. To repeat, in the report view no formatting, cut out paste into Word, formatting there.

I don't have a working printer handy at the moment at home, so I printed to an XPS file. Again, the formatting was ignored. Somehow, Word knows what to do with it.
I have not printed out these parts yet - maybe it outputs to the printer properly, but I would have expected the XPS to format it right. Printing to a PDF also doesn't respect the carriage returns. Still stumped.

UPDATE: If I run the replace method on the field with a visible character, such as a letter, it finds no carriage returns to replace. To verify I was doing it correctly, I replaced a letter in the text with several other characters. It worked. So, literally there are no carriage returns in the text appearing in the field - conclusively they are stripped out. Yet when I cut the text and drop it in word, all the carriage returns are represented. I'm off to a working printer now to see if it actually does print properly.

CONCLUSION THUS FAR: I can cut the text out of the report view and paste it into Word, Notepad ++, and Wordpad and all the formatting is represented. On the screen and a printout from Access omits any formatting. A cut from access and a paste into here shows all the formatting. SEE BELOW. Currently, this seems like some sort of Access bug. I have version 14.0.6129.5000 (64-bit). Any thoughts considered gratefully. FYI I have SP1 installed, and there seem to be no further updates. I have now posted in the MS forum.

-----------------CUT FROM TEXT ON SCREEN----------------
Loan did not Materially Comply with Applicable Underwriting Requirements

This loan did not materially comply with one or more of the applicable underwriting requirements for Credit, Income and Employment, Assets, Collateral, or Closing Conditions.

• Borrower is self-employed partnership established in 3/05 providing only 1yr of business tax returns from 2005. Income is incorrect based on standard income calculations. There is no documentation to see how income was originally calculated. I provided income worksheet using borrowers 50% of partnership income. Borrowers spouse owns the other 50% but is not on the loan so income should not be used. A YTD P&L for 2006 should have been requested due to the time of application of 12/06 and closing in early 2007. Based on standard FNMA income calculations, income should be $2544. Using this qualifying income and including the omitted HOA dues of $32.08 in proposed housing expense, ratio are 56.48/74.52 which exceed the 2% tolerance of the approved DU ratios of 43.12/57.20.

• Assets are documented by current statements. Personal and business assets and no letter from CPA on effect of using business assets. There is no source of funds in the file for large deposits. Borrower's personal acct has deposits ranging from $700. to $10,000. The Business acct has multiple deposits that are not addressed, but her business is a nail salon which is a service business were large and frequent deposits are not uncommon.

[tstoneami]

Mystery solved.
The data source ONLY used line feeds, no carriage returns. I never found CR's in the source so I didn't bother looking for JUST line feeds. In the case of this, each line feed represents a CR and LF. I know this won't help many, but wth.


Regards,

Troy

[June7]

[see me slap forehead] - LOL!

So a Replace on LF character works?