We have a proprietary database (on Oracle) that contains project information. We link to it through a ODBC and in Access we “Get External Data”, and import the tables we need to query. For the most part these queries return text because (I’m assuming) that’s how the original database set them up. However, one of the data fields is pulling from a questionnaire response which must be formatted pretty because the data field comes back filled with things like this:
{\rtf1\ansi\deff0{\fonttbl{\f0\froman Tms Rmn;}{\f1\froman Times;}{\f2\froman Times New Roman;}}{\colortbl\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue127;\red0\green127\blue127;\red0\green127\blue0;\red127\green0\blue127;\red127\green0\blue0;\red127\green127\blue0;\red127\green127\blue127;\red192\green192\blue192;}{\info{\creatim\yr2003\mo9\dy26\hr8\min44\sec7}{\version1}{\vern262367}}\paperw12240\paperh15840\margl1080\margr1080\margt1080\margb1080\deftab720\pard\ql{\f2\fs20\cf0\up0\dn0 \loch\af2 Location 1 }{\par}\pard\ql{\f2\fs20\cf0\up0\dn0 \loch\af2 Location 2}{\par}\pard\ql{\f2\fs20\cf0\up0\dn0 \loch\af2 Location 3}}
The original entry was in a text box in the questionnaire, and read
Location 1
Location 2
Location 3
During the import from the proprietary database, we are not asked about formatting. Is there a way to adjust this format once it’s in Access? We have imported to Excel but since the long string is just text characters, we end up having to manually edit each string.
I don’t really know what your next step is, but I recognzie that stuff as ‘rich text format’ coding. Maybe you can find some library control or object to parse rich text and then just grab the plain, unformatted version of it??
Dear lord, what a mess! - is it a different bunch of formatting in every case, or are you fortunate enough to have your data encompassed by identical jibberish every time? If the latter, it should be fairly easy to stip away the other stuff, or identify the real data. otherwise, it’s going to be a case of (as chrisk says) dumping it into a control that understands and can deal with RTF, then sucking out just the text content.
I just created a quick test database; I made a table with a single field of memo type, then pasted your content into it, then I created a form and dropped a Microsoft RichText control (under ‘more controls’ in the design palette) and an ordinary text box; I attached the RichText control to the field in the table, then added a button that does this:
Me.TextBox.SetFocus
Me.TextBox.Text = Me.RTFBox.Text
and it seems to work OK - at least nearly all of the crap disappeared - I ended up with:
080Location 1
Location 2
Location 3
but I think this might be because your sample hasn’t pasted perfectly.
OK, here we go. The original table the gooblygook got imported to definitely has that field as “memo” type. I created a form including that and a few other fields. I made a button in the form using “Build Event” and “Code Builder” and entered the lines above. I chose Microsoft RichText control and a box appears on the form with “ActiveXCtl13” written in it. I assume that is a command but I don’t have it situated in the right place or attached correctly. What would the next step/corrections be?
