February 1, 2012 at 2:31 pm
I am using MS Access 2007.
There are two tables each having a memo column.
I have written a IIF statement to compare both these columns. If the values match exactly then the query returns the column from the first table. If the values do not match then it must suffix the column text with "Error:".
Issue:
the query returns correct results, however when I try to export the value into Excel 2007, then it trauncates after 255 chars.
Please could you advise on how this can be fixed. This issu occurs only while exporting. In the query window the correct values are returned though !
February 1, 2012 at 3:14 pm
For EXCEL read the following as EXCEL by default will only display a maximum of 255 characters in a text cell, the links below should be of assistance.
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx#top
and
February 1, 2012 at 3:18 pm
What version of Excel are you exporting to? If you are choosing the .xls format it will certainly do that. However if you export to the .xlsx format it should export up to 32767 characters from a memo field. Another possible issue is if you are sorting in your query - in that case it will also truncate to 255 characters. Note that Microsoft Access people are more likely to frequent other forums such as UtterAccess, WindowsSecrets or EileensLounge - you may be able to find the answer to your issue there as well.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 1, 2012 at 10:37 pm
I am using Excel 2007 and MS Access 2007.
I use DoCmd to import the data from 2Excel files into Access... and then compare the text and use Do Cmd to output the data to another Excel 2007.
I think there are two issues =
1. MSAccess while importing data, checks for the first 10 fields while settng the field type in the dynamic table. For the second file it makes a text field and hence accepts only 255 chrs.
2. I could still compare and provide output for the first 255 chars however, while displaying results, I compare teh memo field with the text field and disply the memo field value.
This value gets truncated after export to excel. The query window does show me the complete data from the memo field.
February 2, 2012 at 5:18 am
By DoCmd I presume you are using the DoCmd.TransferSpeadsheet method. If you are importing the Excel file into a new table, Access does indeed check a few rows to determine the data type of each. If on the other hand you create a table with the desired field types, and then import into an existing table, it should import the complete memo field for each row.
I think it would help understand your situation if you could post the code you are using to implement the import and export as well as the SQL string for the query you are using to compare the two tables.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 2, 2012 at 9:00 am
Current issue:
-----------------
Yes I am usinig DoCmd to import the excel files into tables and to export query results into excel.
The table structure for import is not predefined.
For import:
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel12Xml, outsname(i) + "Output", outfadd, True, outsname(i) + "$"
For export:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strEngagementSheet, strPathFile
Also tried the following-
-----------------------
1. I did try creating a table structure and reading cell by cell from excel into the table, however, the recordset that read from teh excel cell (for the memo field) - displayed unidentified characters post 255th position.
2. I also tried to export the data into XML first and then importing XML file into Excel.
I have windows 7 installed and Access 2007 crashes when the XML is saved. I noticed that the XML file was getting saved but it was not well formed. The same code worked on a colleagues machine though. So I do not know what is missing in my machine.
Code for saving XML-
=====================
Dim objOrderInfo As AdditionalData
Dim objOrderDetailsInfo As AdditionalData
Set objOrderInfo = Application.CreateAdditionalData
Set objOrderDetailsInfo = objOrderInfo.Add("EngagementsDifference")
objOrderDetailsInfo.Add "EngagementsDifference"
Application.ExportXML ObjectType:=acExportQuery, DataSource:="EngagementsDifference", _
DataTarget:="EngagementsDifference.xml", _
AdditionalData:=objOrderInfo
Code for importing XML into EXCEL
=========================
ActiveWorkbook.XmlImport URL:= _
ActiveWorkbook.Path + "\EngagementsDifference.xml", ImportMap:= _
Nothing, Overwrite:=True, Destination:=Range("$A$1")
February 2, 2012 at 3:24 pm
I have modified the complete code piece.
Now I have made tables in Access with field type Memo for Description column.
The query has been modifed too.
I need to compare descrioptions from two excel files and display it in excel again.
Making tables helps in terms of textual comparison works for > 255 chars..however, now I see an issue with the output that i see. the excel cell to which the data is exported from access shows junk values after a certain limit. 255 I think.
I tried setting values for this field using mid(Description,0,250) + mid (Description,251,250) etc
I am sure people would have faced this issue earlier. Can someone please helpout.
SAmple of what I see now:
" Core objective assigned to the CRM2 Program was to develop an integrated CRM solution based on PeopleSoft CRM. Application is already live in pr?Interfac????"
February 2, 2012 at 3:31 pm
Further information on teh above.
1. The query does not have any group by clause.
2. The only manipulation with the memo field is the prefixing with text "Error:" when the description in both input files do not match.
3. when i change the cell format in excel to "General" teh problem still appears.
If changed to "Text" then i see ###########
February 2, 2012 at 3:59 pm
One question I should have asked is whether you have Service Pack 2 of Office 2007 installed? There were some fixes to the import and export routines in that update. I'm attempting to duplicate you situation, but other things keep interrupting.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 2, 2012 at 4:27 pm
Microsoft Office Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6562.5003)
February 2, 2012 at 4:51 pm
Well, it looks like you have the latest Office service pack. Unfortunately I'm not able to duplicate your problem when using Memo fields - as long as I don't do any sorting or grouping or using joins with the memo field. I think it must come from the IIF() function that you are using, but why that should affect the output is a mystery. Hopefully somebody else will have a suggetion to try.
As an aside, is there a reason for doing this in Access rather than Excel? It's fairly easy to creat an Excel workbook with references other workbooks and will tell you where there are differences, etc.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 7, 2012 at 4:31 am
Thats for your time... yes there was a reason to use Access as data manipulation which would be required later would be easier using queries.
I fixed my issue... by revamping the total code.
1. I created tables in access.. whereever I knew that the field size would be > 255, I made those column memo.
2. I wrote a VBA script to read the data cell-be-cell from excel file and insert into table.
3. on the click of another button, teh VBA code uses DoCmd and executes a query.. In the query - I have broken the Description filed into 5 - so the queyr looks somewhat like -
....
Description, MID(Description,1,250) as D1, MID(Description,251,250) as D2,MID(Description,501,250) as D3, MID(Description,751,250) as D4,
....
4. After populating the results in excel file using DoCmd - I wrote VBA script to
1. concatenate the D1,D2,D3,D4 values and display that in the Description field.
2. delete the D1,D2,D3,D4 fields. The deletion was sequenced in the reverse order to ensure no column reference changed post deletion of columns.
Thanks all for your help.
March 5, 2019 at 1:06 am
npranj - Wednesday, February 1, 2012 2:31 PMI am using MS Access 2007.There are two tables each having a memo column.I have written a IIF statement to compare both these columns. If the values match exactly then the query returns the column from the first table. If the values do not match then it must suffix the column text with "Error:". Issue:the query returns correct results, however when I try to export the value into Excel 2007, then it trauncates after 255 chars.Please could you advise on how this can be fixed. This issu occurs only while exporting. In the query window the correct values are returned though !
Try to do it in Sql Server
March 6, 2019 at 9:04 am
Just zooming by, but I've overcome that 255 xls issue by exporting to tab-separated values (.txt, .tsv) and opening/importing with Excel. May not be applicable to your situation, but wanted to toss in the ring in case.
March 6, 2019 at 12:07 pm
Just noting that this is a thread where the last previous post was 7 years ago, so chances are his problem was solved a LONG time ago.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply