Why is one field in this query being made too small (1 Viewer)

[darbid]

I realise my query may be complicated but I want to just show the full query. This is copied from a query where I am just testing it.

The issue I have is that the field derwent_abstract can have up to maybe 500 to 700 characters. But the result of the below SQL always cuts the size to something a lot smaller - (my test show around 250)

Why is this?

SELECT * INTO tbl_questionnaire
FROM [SELECT IIf(IsNull(connect.srf_akz), em.akz, connect.srf_akz) as patent_akz, em.status, em.decision_number, em.decision_date, em.work_around_ability, em.attractive, em.used_by_third_party, em.total_eval, em.can_use, em.eval_reason, em.use_inhouse, em.product, em.decider, em.bkz_id, em.bkz_questionaire, em.hierarchie_level_2, em.hierarchie_level_3, em.hierarchie_level_4, em.hierarchie_level_5, em.piv, em.piv_name, null as pik_coordinator, null as pik_name, null as fal_pik, null as fal_name, null as l_akz, null as derwent_title, [COLOR=Red]null as derwent_abstract[/COLOR], null as date_of_lapse, null as countries, null as only_siemens_in_charge, em.type, em.em_akten_id, connect.srf_akten_id
FROM (em
LEFT JOIN connect
ON em.em_akten_id = connect.em_akten_id)
UNION select srf.akz as patent_akz, srf.status, srf.decision_number, srf.decision_date, srf.work_around_ability, srf.attractive, srf.used_by_third_party, srf.total_eval, srf.can_use, srf.eval_reason, srf.use_inhouse, srf.product, srf.decider, srf.bkz_id, srf.bkz_questionaire, srf.hierarchie_level_2, srf.hierarchie_level_3, srf.hierarchie_level_4, srf.hierarchie_level_5, srf.piv, srf.piv_name, srf.pik_coordinator, srf.pik_name, srf.fal_pik, srf.fal_name, srf.l_akz, srf.derwent_title, [COLOR=Red]srf.derwent_abstract[/COLOR], srf.date_of_lapse, srf.countries, srf.only_siemens_in_charge, srf.type, null, srf.srf_akten_id
FROM srf]. AS [%$##@_Alias];

 

[spikepl]

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

in your query, try UNION ALL, so that the various records do not need to be compared

 

[darbid]

I am not gooe with SQL - I tried a Union All but it did not change it. Could you please suggest something else?

EDIT: Sorry - hold on a second - my test just worked with Union All. I will keep testing and let you know

 

[spikepl]

I am not too sure, but I would try skipping the Memo-field in the UNION-query and make a new query with the modified UNIT-query as source, and an added column with the memo-field. Alternatively, skip the memo alltogether, and use a Dlookup on the form.

Perhaps somebody else will have a better solution.

 

[gemma-the-husky]

I assume the field in tbl_questionairre is a memo, and not text?

try a select query, instead of a insert query. Add a column to get the length of the memo field. Do you get any problems with the record lengths in the select query?

 

[darbid]

I assume the field in tbl_questionairre is a memo, and not text?

try a select query, instead of a insert query. Add a column to get the length of the memo field. Do you get any problems with the record lengths in the select query?

Yes tbl_questionnaire (which is a linked sql server table) is large enough.

Thank you guys. I seemed to have solved it with a "Union All" and using a select.

 

[boblarson]

The key with a memo field is to NOT include it when using criteria and then you join it back in later with the query that has the criteria. As noted by Allen Browne's website, if you have criteria on a query and the memo field is included it will truncate to 255 characters. To get around that you simply create a query with all fields except the memo field and then you create a second query with that first query and the original table in it and joined on the applicable fields of the first query which would be limiting the records.