Database memo fields truncated to 255 char - help! 3

[Whymars]

Hi folks, first time post here, so please be gentle I have a database that contains a table with a number of memo-type fields (text1, text2, text3). These can't be text-type because they can potentially contain upwards of a few thousand characters (255 char limit on text).

Now, I've got a form that we use to enter data into the table, and view it, and it all gets saved fine (that is, it's all there when I change or restart) and I can see it all in the form. When I open a query that includes these fields (the SQL query is in a variable called sql_all) through:

Dim rs_gr As DAO.Recordset
Set rs_gr = db.OpenRecordset(sql_all).Clone

I can address the text fields with rs_gr("text1") etc, but I can only get the first 255 characters out this way.

I initially thought this was a limitation of the string-type variable I was putting the data into, but it seems that Len(rs_gr("text1")) returns 255, even before any other variables are involved.

I'm looking for some insight into how these different ways of accessing the dataset (through a form, or VBA) affect what comes out. Ultimately I'd like some advice on how I might fix the problem - how I can use VBA to get the full length memo into a variable (and what kind of variable - and how to convert that to a string, I guess). Perhaps I should be using a different data type in my table? I am using the variables to replace bookmarks and output a word document. This whole process works fine (without errors, even) at the moment, but dutifully outputs .DOCs with only 255 characters in each of the text areas.

I hope someone can help me - I can't imagine this is a novel problem, but I can't seem to find any help on it.


Thank you all in advance,
Best regards

Sandy

 

[Golom]

The general rule with Memo fields is that, if your SQL attempts to do anything with the value of the field other than just retrieve it, then it will be truncated to 255 characters.

For example,

Where [Text1] LIKE '*ABC*'

will result in the [Text1] field being truncated.

There's a possibility that using the Clone of a recordset rather than the recordset itself could have some unfortunate side-effects on Memo fields. (I'm not at all sure about that however.)

Can you post your SQL and identify those fields that are Memo fields?

 

[Whymars]

Hi, thanks for your reply - here's the SQL. Sorry it's a bit of a stinker but I couldn't think of a way to simplify it.

The table the memos come from is called gr_gr-session_data. Most of the other sources referred to in the SQL are queries themselves. text1, text2 and text3 are the names of the memo fields. I've tried accessing the recordsets directly (that is, not with Clones) and it still truncates it. The query gets all the records with a certain session_id (variable called session_id in WHERE portion) from that table, along with bits and pieces from a bunch of other tables and queries that I need to populate the output.

sql_all = "SELECT DISTINCT p_names_primary.persname, p_names_primary.surname, [gr_gr-session_data].master, [gr_gr-session_data].gr_data_id, [gr_gr-session_data].session AS session_id, [gr_all sessions-summary].date_scheduled, [gr_all sessions-summary].group_name, [gr_all sessions-summary].module_name, [gr_all sessions-summary].session_no, [gr_gr-session_data].attended, [gr_gr-session_data].text1, [gr_gr-session_data].text1_add, [gr_gr-session_data].text2, [gr_gr-session_data].text2_add, [gr_gr-session_data].text3, [gr_gr-session_data].text3_add, [gr_gr-session_data].venue, [p_one_keyworker_Supervising SW].sw_persname AS cm_persname, [p_one_keyworker_Supervising SW].sw_org AS cm_org, [p_one_keyworker_Supervising SW].sw_surname AS cm_surname, p_all_workers_1.persname AS auth_persname, p_all_workers_1.surname AS auth_surname, p_all_workers_1.[short name] AS auth_org, p_all_workers_3.persname AS fac1_persname, p_all_workers_3.surname AS fac1_surname, p_all_workers_2.[short name] " & _

"AS fac1_org, p_all_workers_2.persname AS fac2_persname, p_all_workers_2.surname AS fac2_surname, p_all_workers_3.[short name] AS fac2_org, p_all_workers.persname AS fac3_persname, p_all_workers.surname AS fac3_surname, p_all_workers.[short name] AS fac3_org " & _

"FROM [p_one_keyworker_Supervising SW] RIGHT JOIN ((p_all_workers AS p_all_workers_1 RIGHT JOIN (p_all_workers AS p_all_workers_3 RIGHT JOIN (p_all_workers AS p_all_workers_2 RIGHT JOIN (p_all_workers RIGHT JOIN (p_names_primary RIGHT JOIN [gr_gr-session_data] ON p_names_primary.pers_id = [gr_gr-session_data].client) ON p_all_workers.pers_id = [gr_gr-session_data].fac3) ON p_all_workers_2.pers_id = [gr_gr-session_data].fac2) ON p_all_workers_3.pers_id = [gr_gr-session_data].fac1) ON p_all_workers_1.pers_id = [gr_gr-session_data].worker) LEFT JOIN [gr_all sessions-summary] ON [gr_gr-session_data].session = [gr_all sessions-summary].gr_session_id) ON [p_one_keyworker_Supervising SW].pers_id = [gr_gr-session_data].client " & _

"WHERE ((([gr_gr-session_data].master)=No) AND (([gr_gr-session_data].session)=" & session_id & "));"

 

[PHV]

Remove the DISTINCT predicate.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

A future reference
Truncation of Memo fields

http://allenbrowne.com/ser-63.html

 

[Whymars]

And the clouds parted and the golden light of understanding illuminated the code! Thanks Golom for your prod, PHV for your answer, fneily for showing the working-out! Very much appreciated indeed. I'm working this VBA business out myself as I go and it's this kind of support that makes it possible.


Very best regards, and thanks again

Sandy