Exporting Truncating Memo Fields

May 26, 2006

I have a query that is displaying exactly the correct results. However, when I export this to a text/tab delimited file (or even and XLS file) it truncates the memo field to 256 characters in the export file. I am sure it has something to do with this memo field being defined in part by a custom function. Below is the query and the function. The field in question is the "Formatting(First(description_text)) AS prod_Description" column. Any thoughts? Note: I know that if I don't perform the "First" on this memo field, during the group by, the query would truncate this to 256 characters becuase it has to be in the Group By clause. But by using the First function, this field does not need to be included in the group by and there for the query does not truncate it (even though the exporting does).

SELECT ProductList.cin_id AS prod_ID, Formatting([desc]) AS prod_Name, "" AS prod_Flag, "" AS prod_OverrideName, "" AS prod_SortName, Formatting(First(description_text)) AS prod_Description, "" AS prod_Bullets, ProductList.mfr AS prod_Mfr, "" AS prod_itemSort, "" AS prod_ProdGroup, "" AS prod_SubprodSequence, "" AS prod_Layout, "" AS prod_BaseProductID, "" AS prod_ItemSubheadAttr, "" AS prod_Keywords, "" AS prod_URL, "" AS prod_Type
FROM ProductList
GROUP BY ProductList.cin_id, Formatting([desc]), ProductList.mfr
HAVING (((ProductList.cin_id)<>''));

****

Public Function Formatting(Text As String) As String
Dim outString As String
outSring = ""
If Len(Text) > 0 Then
outString = Replace(Text, "<b>", "{")
outString = Replace(outString, "</b>", "}")
outString = Replace(outString, "<i>", "{i")
outString = Replace(outString, "</i>", "}")
outString = Replace(outString, "°", "°")
outString = Replace(outString, "™", "™")
outString = Replace(outString, "©", "©")
outString = Replace(outString, "'", "'")
outString = Replace(outString, "&reg;", "®")
outString = Replace(outString, "<sub>", "{^/")
outString = Replace(outString, "</sub>", "^")
End If
Formatting = outString


End Function

View Replies


ADVERTISEMENT

Queries :: Query Truncating Memo Fields

Dec 16, 2014

I have a query that is truncating a memo field to 255 characters. There is no distinct, group by, format, union or concatenate in the query which are the common cause for truncation.The truncated memo field is comments.

Code:
SELECT HearingAuditTbl.CASE_NUMBER,
Null AS appealcaseid,
HearingAuditTbl.HEARING_ALJ,
Null AS DecisionCode,
HearingAuditTbl.DECISION_DT AS ALJ_Date,
HearingAuditViolations.VIOLATION_NO,
HearingAuditViolations.COMMENTS,
"CATEGORY_B" AS CATEGORY,
NOW() AS DATE_ADDED

[code]...

View 3 Replies View Related

Truncating Memo Field

Jan 26, 2006

I am using

DoCmd.OutputTo to export a query but this truncates the memo fields, but in the db window,right clicking and choosing to export does not truncate the data.


How do I code to output all the data without truncating?

View 1 Replies View Related

Memo Field Truncating :s

Nov 23, 2005

Hi,

Ok, firstly i have search google and this forum from top to toe and no-1 has an answer that works for me.

I am running a query, now i have completely simplified it. The query is now just picking up a field called recommendations. Recommendations is a memo field with no formating or index on at all. Now, when i run the query as normal it works fine... everything is there. But when i have to group by it. It cuts it off at 255 charactors. The thing is i need to do some sums and counts within the query aswell so it has to be.

I have checked microsoft and there suggestions are not any use.

Any ideas???

View 2 Replies View Related

Memo Truncating On Form

Aug 2, 2006

I searched the forum and realize that there are many posts on this subject, mostly related to exporting the data on reports and such. However, the problem that I am having is within the form. I have two memo fields on my form, named Memo1 and Memo2. Their data source is on a table where both fields there are also memo fields, called Notes and More Notes. I know a memo field can contain up to 65,000 characters, but mine don't seem capable of this and are truncating the data without warning the user, resulting in notations getting "lost". However, when I open the table that the form is bound to and check the Notes field, it DOES contain all the right data PRIOR to truncating. What's happening here? I've reviewed RG's link and have verified that I am not formatting or sorting on these fields, and I've utilized the ZOOM function, and I've checked the names of my fields. The form is built directly from the table, not a query, so I can't understand why this is happening. Starting to get very frustrated. Any suggestions would be welcome. Thanks for your advice.

View 2 Replies View Related

Truncating Of Memo Data Type

Oct 5, 2005

I have a table in my database to track survey responses. Some of these responses can be quite long so I am using the memo data type. I have some queries set up to display these responses but the queries truncate the responses to 255 characters. Is there a way to stop the query from truncating? Thanks in advance.

View 2 Replies View Related

Tables :: Excel Memo Field Truncating

Apr 18, 2013

I have a strange issue where im importing an excel using docmd.transferspreadsheet. I have a memo field which is importing fine and is not being truncated but I have another which is being truncated every time. The destination field is set to memo and the top cell in the excel is over 255 chars yet it still truncates. The other column does not truncate for some reason.

View 2 Replies View Related

Exporting Memo Field (&gt;255 Char) Into Excel

Sep 28, 2005

Hi,

I've read a bit on exporting Access fields longer than 255 characters (Memo) into Excel, and the general concensus is that it cannot be done.

All I need to do is run a report, right click the report, and select Export from the menu.

However, if it can be done using a query, any assistance or pointing in the right direction will be greatly appreciated.

Thanks..

Chris

View 1 Replies View Related

Memo Field Display & Convert Text When Exporting?

May 29, 2005

I use a memo field in a form. This memo field is used to store "steps" for doing something.

To keep things neat, I use ENTER between the steps so there is a blank line between each. This looks good in the form, however... I export this information (from the table) to a CSV/text file to use online. My online script requires that each record occupy only one line. But, the exported file has separated the "steps" into multiple lines just as it appears on the form.

I would like to use the HTML tag <BR> to separate the line feeds in the CSV file. I can enter these in the FORM instead of pressing ENTER, but then the form looks bad - harder to read.

Is this possible to do with code or filters or ??

Thanks for the help!
Mike

View 7 Replies View Related

Memo Fields

Aug 15, 2007

Hi All,

This is probably the simplest question to answer but alas I can't find the answer in any of my books!

I have a memo field which when made into the form is for a comments box (therefore has to be a memo box). However, the end user wants to be able to type in the comments and have this is paragraphs but of course the field doesn't want to do it.

I've tried various commands in the properties and also in the formats etc. and now I'm at a loss - can someone please give me some much needed guidance, and I really am sick of looking at this database and would like to get rid of it!

Thanks in advance guys.

Donna :eek:

View 2 Replies View Related

Memo Fields

Jan 18, 2005

How can you use a memo field in a combo box.

How can you edit a memo field used by the combo box once it has been selected in a form.

View 1 Replies View Related

Memo Fields

Nov 2, 2006

I have a memo field in my database, as the data is too long to be a text field. The data in this field has been pasted in from other sources, and somehow it seems to have remembered the original pasted formatting, which was like a newspaper column. So when I include this field on a report, I get a thin column of text, even though the text box on my report is much wider. Is there a way of getting the text from my memo field to wrap to fill the width of my text box, so I get a neat-looking report. Thanks.

View 3 Replies View Related

Hyperlinks In Memo Fields

Dec 9, 2007

I have been asked if is possible to highlight some text in a memo field and make it a hypertext link.

A memo field can be changed to a hyperlink field, this is not what is wanted.

View 1 Replies View Related

Compare Memo Fields

Feb 5, 2008

Can someone tell me if this can be done somehow.

I have a date field, ID field and a memo field (I have to have this memo field).
I want to compare the memo fields by two certain dates and find out what doesn't match and if the ID field is blank. I have been trying with quaries but no luck.

I am not sure what forum this would fall under so I put it in general. feel free to move.

View 7 Replies View Related

Should I Avoid Memo Fields

Jul 18, 2005

I've read on this forum that memo fields can be a spot of bother. I only saw this after I put several memo data types in my tables. I wasn't going to do that much with them; it was intended as a electronic scratch pad of sorts for the user. Where does the trouble arise from with them? Should I just leave them or should I change them to text fields with 255 chars and hope that's good enough?

scratch

View 1 Replies View Related

Memo Fields/boxes

Jan 23, 2006

I have a memo field on a form that I only want 800 characters. (text fields don't seen to work). I'm looking for a piece of code that monitors the typing into a memo field and alerts the user that they are overe quota for that field when 800 characters are passed.

Any ideas are appreciated.

View 3 Replies View Related

Querying Memo Fields

Mar 22, 2007

Hello There,

I wonder if someone can clear up a problem I am having. We have a system under development which is heavily dependent on an existing MS Access database. Some of the queries in the Access version of the application query Memo fields and they work fine within Access.

If I try to use exactly the same query from an ASP/Vbscript web page the query does not return the expected results. I am using ADO and an ODBC DSN connection, all of the other queries seem to work fine.

I have a vague recollection of being told that you can not query Memo fields through an ODBC DSN, or that not all of the Memo field is queried, only the first 255 characters. Can anyone confirm this is true. I have tried searching the web, and some references that I have available, but it seems that there is nothing out there to confirm my suspicions.

Any direction anyone could give me would be hugely appreciated.

Thanks in advance.

View 4 Replies View Related

Auto Expand Memo Fields

Apr 19, 2006

I'm working on a sub-form set to continuous and would like to amend a memo filed so that it autoexpands when entered. Have tried amending the height of the field through the on enter an on exit functions but this alters the size of the form.

Any clues would be much appreciated.

Fozi

View 1 Replies View Related

Memo Fields - Order Of Data

Dec 6, 2007

Hello people.

Im just after a bit of advice please??

I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:

Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String

Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)

Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"


Thanks folks...

View 2 Replies View Related

Unable To Sort Memo Fields?

Mar 16, 2006

A quick question... is it possible to sort a table by a memo field? I have a table and am able to sort by other, non-memo fields, however when I place my cursor in the memo fielld I would like to sort, the sorting option becomes 'greyed-out'. Is there a way around this?

View 7 Replies View Related

Can I Merge 2 Txt Fields To 1 Memo Field

Apr 6, 2006

Hi,

I have an old database for generating service reports which has:
Report No
Date
Customer
Site

In separate fields which is fine

but also

wrk1
wrk2
wrk3
etc.

with a separate field for each printed line on the page
I would like to merge all these into one memo field while retaining the previous data which goes back 15 years (it came from a MSWorks 2.0 DOS db which is the reason for the weird structure I think)

Is there any way to write a macro or code to selectively merge some but not all fields in a table?

I've already copied the db and modified it after clearing all the old data, it works fine but I'd like to have access to the old data in the new format

TIA

Kim

View 3 Replies View Related

Memo Fields Mess Up My Database

Aug 8, 2007

Hello,

when I update a form with a new record, that record will copy to any previous record that I click in the same form.

This only happens when I'm adding records to a table that contains a memo field...

Anything I need to keep in mind about memo fields?

Kind regards,
Hans B.

View 1 Replies View Related

Modules & VBA :: Inner Join On Memo Fields

Apr 9, 2014

I've got an issue pertaining to inner joins within an update query. This query will not run now (giving the expected "must use an updateable query" error) due to two fields involved in one of the inner joins are now both memo fields.

Is there any way (and I assume there must be in vba) to recreate this query so it will run with inner joins involving memo fields?If not, is there a way in vba to do a find and replace within a table? For example, replace the "A" in "A-001" with "B"?

View 5 Replies View Related

Exporting Certian Fields

Jul 1, 2005

I have a table with the fields username, password, userlevel, blocked, name.

I want to be able to export the fields username and password to a text file in the format of:
USERNAME1|PASSWORD1
USERNAME2|PASSWORD2
USERNAME3|PASSWORD3
USERNAME4|PASSWORD4

Can anybody help me as i am not sure on how to do this!
Thanks in advance
Aden

View 13 Replies View Related

Using Text Files Instaed Of Memo Fields

Mar 27, 2007

I have a large Db Which contains A Couple of Memo fields which due to the amount of data being stored in the Memo fields is starting to show signs of slowing down.One table contains 75,000, with all memo fields above 255.I wont have a problem with adding the extra code but was wondering if there is anything that may cause me a problem or if there's any reason(s) why I shouldn't follow this path.Many ThanksMickP.S. One Option would be to create 1-1 Tables and move the memo fields into the new tables that way I would still have an easy way of serching but it would reduce the size of the main table also none of the memo fields are included in any of the querys used, the problem with speed seems to be adding data I have checked the indexs which seem OK I can't remove any indexes Otherwise it slows down searching ACT.A copy Of the DB Is available here but it is an 11Mb Download should anybody wish to take a look at the tables structure and be able to advise on any imporvments that would be great please remember it's designed to store very large amounts of data.http://chartheaven.9.forumer.com/index.php?showtopic=107best wishesmick

View 3 Replies View Related

Tables :: Possible To Export Memo Fields To Excel Via VBA?

Sep 4, 2014

I'm trying to export my table using the following code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "incidents", "c:Incidents.xls", True

But unfortunatly I get truncated errors upon export via the vba above. is it not possible to export memo fields to excel via vba?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved