...The memo field has all text data in it & it is often more than 255 chars.
Hi again Shaq. In the circumstances (and against my better judgment!) the simplest solution I can think of is a brute-force one of adding additional fields that split up your memo field into a series of sub-fields, each of no more than 255 characters. It's not elegant at all, but it would allow you to retain the simple Excel export you currently use. Perhaps other contributors will be able to suggest better ways...
Although you have mentioned that the memo field is more than 255 characters you haven't said how long it is in typical use. You can use the Len() string function to find out, if you are at all familiar with using functions in queries.
In the DoCmd line of your code the exported table is called
test2. If this is a query you can add new columns beside your memo field to split up the contents of your memo. If
test2 is a table then you need to start by creating a query based on test2 so that you can add the additional memo fields. Include all the fields that are currently in test2.
Assuming that your memo is no more than 4x255 characters long (1020 chars), open the query in design view and add three blank columns beside your memo field. Add the following field names in the three blank columns, in the rows marked field: substituting the name of your current memo field in each case:
Memo Pt 2: Mid$(Nz([name of memo field]), 256, 255)
Memo Pt 3: Mid$(Nz([name of memo field]), 511, 255)
Memo Pt 4: Mid$(Nz([name of memo field]), 766, 255)
The mid$ (mid-string) function in each of these will extract 255 characters from your memo field at positions 256, 511, 766 respectively. Nz() ensures that if the field is null Mid$ is still fed by a string (although zero-length). Mid$ does not fail if there are no characters to return - it just returns a zero-length string.
If you added the three columns to a new query based on test2 save it under a suitable name (such as qryTest2) then change your Docmd statement to refer to qryTest2 instead of test2.
These are the only changes you will need to make to at least be able to export more of your memo field in this brute-force way. If however you need more characters still I think a very different solution will be required - because it is not really appropriate to go on taking 255 character chunks out of your memo field just because that is Excel's text limit.
-Stewart
ps if you are working in SQL view you can add the following to your select statement to do the same job as adding the three columns above:
-
-
select ..., [name of memo field], Mid$(Nz([name of memo field]), 256, 255) as [Memo Pt 2], Mid$(Nz([name of memo field]), 511, 255) as [Memo Pt 3], Mid$(Nz([name of memo field]), 766, 255) as [Memo Pt 4], ...
-
19
