0

I'm using Access 2000 and I have a query like this:

SELECT function(field1) AS Results FROM mytable;

I need to export the results as a text file.

The problem is: function(field1) returns a fairly long string (more than 255 char) that cannot be entirely stored in the Results field created from this query.

When i export this query as a text file, i can't see the string entirely. (truncated)

Is it possible to cast function(field1) so it returns a Memo type field containing the string ? Something like this:

SELECT (MEMO)function(field1) AS Results FROM mytable;

Do you know others solutions?

unkulunkulu
  • 11,576
  • 2
  • 31
  • 49
ponponke
  • 113
  • 2
  • 3
  • 11
  • If you store that query in Access and then open it in datasheet view can you see `Results` that are longer than 255 characters? (Hint: You may need to use the `Shift+F2` "Zoom" keyboard shortcut to see all of the text.) – Gord Thompson Apr 26 '13 at 15:35
  • 2
    This may be Access version dependent. I tested this in Access 2013 and it definitely returns strings of over 1500 characters. What version are you using? – Dan Metheus Apr 26 '13 at 16:33

2 Answers2

1

There is an official microsoft support page on this problem: ACC2000: Exported Query Expression Truncated at 255 Characters

They recommend that you append the expression data to a table that has a memo field, and export it from there. It's kinda an ugly solution, but you cannot cast parameters to types in MS Access, so it might be the best option available.

NMR
  • 166
  • 8
0

i don't know how to do quite what you're hoping (which makes sense) but a possible alternative could be to create 2 or 3 fields (or separate queries) and extract different portions of the text into each then concat after retrieved. 

pseudo: concat((chars 1-255) & (chars 256-510) & (chars 511-etc...))

edit: it's odd that a string longer than 255 is stored but it's not memo. what's up there? another alternative, if you have access to the db, is change the field type. (backup the db first!)

wazz
  • 4,953
  • 5
  • 20
  • 34