Reply to thread

Message: [QUOTE="Clifford Bass, post: 12717349"] Hi Dale, That is a problem that I have hassled over several times: Union queries returning the wrong field type due to null values. It is related to the first select in the union. That one is the one that determines the field types. How does the query processor decide what type "Null as Org_ID" is? The solution, which just came to me as I read your question is this: Create a table, maybe named tblFIELDS, with each of the different types of fields defined. So you might have INTEGER_FIELD, TEXT_FIELD, BOOLEAN_FIELD, DOUBLE_FIELD, CURRENCY_FIELD and so on. The table will remain empty. As such there is no need for an index. Then use it as your initial union item like this: SELECT INTEGER_FIELD AS LO_ID, INTEGER_FIELD AS Org_ID, TEXT_FIELD AS Field1, TEXT_FIELD AS Field2, INTEGER_FIELD AS Sort_Order FROM tblFIELDS union SELECT tbl_Learning_Objectives.LO_ID, Null, tbl_Learning_Objectives.Learning_Objective, null, Sort_Order FROM local_Parameters INNER JOIN tbl_Learning_Objectives ON local_Parameters.DefaultYear = tbl_Learning_Objectives.PlanYear UNION SELECT 0, tbl_Organizations.ID, tbl_Organizations.Org_Abbr, tbl_Organizations.Org_Name, NZ([Lab_Sort],99)*100+NZ([Org_Sort],99) FROM tbl_Organizations) as LOs_and_Orgs ORDER BY Sort_Order, Field1 Note that while it should not matter with Access, but just to be sure, I removed all of the ...As Field Alias... items from the succeeding selects. They are not really needed anyway. Let me know if that does the trick. Clifford Bass [/QUOTE]

Verification