Access 2013
I have several logs that are consolidated using a UNION ALL query that is written by a macro.
The resulting Query is as follows:
SELECT [zData Navy FY15 AFS].[Journal Voucher ID], Count([zData Navy FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 AFS].[Table Source]) AS [Table Source], First([zData Navy FY15 AFS].[dBranch]) AS [dBranch], First([zData Navy FY15 AFS].[DB_ID]) AS [DB_ID], First([zData Navy FY15 AFS].[Source]) AS [Source], First([zData Navy FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 AFS].[Reason]) AS [Reason], First([zData Navy FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 AFS].[Created Date]) AS [Created Date], First([zData Navy FY15 AFS].[Approval Date]) AS [Approval Date], First([zData Navy FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 AFS].[SUS ID]) AS [SUS ID], First([zData Navy FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 AFS].[Quarter]) AS [Quarter], First([zData Navy FY15 AFS].[FY]) AS [FY], First([zData Navy FY15 AFS].[Record Use]) AS [Record Use], First([zData Navy FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 AFS].[Reason])) AS [Length] FROM [zData Navy FY15 AFS] GROUP BY [zData Navy FY15 AFS].[Journal Voucher ID], [zData Navy FY15 AFS].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BBA GF].[Journal Voucher ID], Count([zData Navy FY15 BBA GF].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BBA GF].[Table Source]) AS [Table Source], First([zData Navy FY15 BBA GF].[dBranch]) AS [dBranch], First([zData Navy FY15 BBA GF].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BBA GF].[Source]) AS [Source], First([zData Navy FY15 BBA GF].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BBA GF].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BBA GF].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BBA GF].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BBA GF].[Reason]) AS [Reason], First([zData Navy FY15 BBA GF].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BBA GF].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BBA GF].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BBA GF].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BBA GF].[Created Date]) AS [Created Date], First([zData Navy FY15 BBA GF].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BBA GF].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BBA GF].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BBA GF].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BBA GF].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BBA GF].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BBA GF].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BBA GF].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BBA GF].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BBA GF].[Quarter]) AS [Quarter], First([zData Navy FY15 BBA GF].[FY]) AS [FY], First([zData Navy FY15 BBA GF].[Record Use]) AS [Record Use], First([zData Navy FY15 BBA GF].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BBA GF].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BBA GF].[Reason])) AS [Length] FROM [zData Navy FY15 BBA GF] GROUP BY [zData Navy FY15 BBA GF].[Journal Voucher ID], [zData Navy FY15 BBA GF].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BE].[Journal Voucher ID], Count([zData Navy FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BE].[Table Source]) AS [Table Source], First([zData Navy FY15 BE].[dBranch]) AS [dBranch], First([zData Navy FY15 BE].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BE].[Source]) AS [Source], First([zData Navy FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BE].[Reason]) AS [Reason], First([zData Navy FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BE].[Created Date]) AS [Created Date], First([zData Navy FY15 BE].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BE].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BE].[Quarter]) AS [Quarter], First([zData Navy FY15 BE].[FY]) AS [FY], First([zData Navy FY15 BE].[Record Use]) AS [Record Use], First([zData Navy FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BE].[Reason])) AS [Length] FROM [zData Navy FY15 BE] GROUP BY [zData Navy FY15 BE].[Journal Voucher ID], [zData Navy FY15 BE].[Record Use]
UNION ALL
SELECT [zData Navy FY15 WCF BBA].[Journal Voucher ID], Count([zData Navy FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData Navy FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData Navy FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData Navy FY15 WCF BBA].[Source]) AS [Source], First([zData Navy FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 WCF BBA].[Reason]) AS [Reason], First([zData Navy FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData Navy FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData Navy FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData Navy FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData Navy FY15 WCF BBA].[FY]) AS [FY], First([zData Navy FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData Navy FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 WCF BBA].[Reason])) AS [Length] FROM [zData Navy FY15 WCF BBA] GROUP BY [zData Navy FY15 WCF BBA].[Journal Voucher ID], [zData Navy FY15 WCF BBA].[Record Use]
UNION ALL
SELECT [zData USMC FY15 AFS].[Journal Voucher ID], Count([zData USMC FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 AFS].[Table Source]) AS [Table Source], First([zData USMC FY15 AFS].[dBranch]) AS [dBranch], First([zData USMC FY15 AFS].[DB_ID]) AS [DB_ID], First([zData USMC FY15 AFS].[Source]) AS [Source], First([zData USMC FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 AFS].[Reason]) AS [Reason], First([zData USMC FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 AFS].[Created Date]) AS [Created Date], First([zData USMC FY15 AFS].[Approval Date]) AS [Approval Date], First([zData USMC FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 AFS].[SUS ID]) AS [SUS ID], First([zData USMC FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 AFS].[Quarter]) AS [Quarter], First([zData USMC FY15 AFS].[FY]) AS [FY], First([zData USMC FY15 AFS].[Record Use]) AS [Record Use], First([zData USMC FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 AFS].[Reason])) AS [Length] FROM [zData USMC FY15 AFS] GROUP BY [zData USMC FY15 AFS].[Journal Voucher ID], [zData USMC FY15 AFS].[Record Use]
UNION ALL
SELECT [zData USMC FY15 BE].[Journal Voucher ID], Count([zData USMC FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 BE].[Table Source]) AS [Table Source], First([zData USMC FY15 BE].[dBranch]) AS [dBranch], First([zData USMC FY15 BE].[DB_ID]) AS [DB_ID], First([zData USMC FY15 BE].[Source]) AS [Source], First([zData USMC FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 BE].[Reason]) AS [Reason], First([zData USMC FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 BE].[Created Date]) AS [Created Date], First([zData USMC FY15 BE].[Approval Date]) AS [Approval Date], First([zData USMC FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 BE].[SUS ID]) AS [SUS ID], First([zData USMC FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 BE].[Quarter]) AS [Quarter], First([zData USMC FY15 BE].[FY]) AS [FY], First([zData USMC FY15 BE].[Record Use]) AS [Record Use], First([zData USMC FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 BE].[Reason])) AS [Length] FROM [zData USMC FY15 BE] GROUP BY [zData USMC FY15 BE].[Journal Voucher ID], [zData USMC FY15 BE].[Record Use]
UNION ALL
SELECT [zData USMC FY15 WCF BBA].[Journal Voucher ID], Count([zData USMC FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData USMC FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData USMC FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData USMC FY15 WCF BBA].[Source]) AS [Source], First([zData USMC FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 WCF BBA].[Reason]) AS [Reason], First([zData USMC FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData USMC FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData USMC FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData USMC FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData USMC FY15 WCF BBA].[FY]) AS [FY], First([zData USMC FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData USMC FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 WCF BBA].[Reason])) AS [Length] FROM [zData USMC FY15 WCF BBA] GROUP BY [zData USMC FY15 WCF BBA].[Journal Voucher ID], [zData USMC FY15 WCF BBA].[Record Use];
The "Reason" field is the Memo field.
The SQL Statement is saved as the query: "Data Export" and then a table us built using:
DoCmd.RunSQL "SELECT * INTO [DATA OUTPUT TABLE] FROM [Data Export];"
The individual select statements yield results w/ the proper length, and the [Length] field is populated with the length of the original string, but the UNION ALL query returns <=255 characater strings. Its like the "Data Export" Recordset is defaulting to Short Text.
Your REASON field is a calculated field First([zData Navy FY15 AFS].[Reason]) AS [Reason]- not an actual memo field
You need to help your UNION ALL query by telling it that it is a memo field
Add a blank row as the first SELECT clause and make REASON an actual memo field
How about his last suggestion about adding an empty row to the top - copy and paste this to the top of your query
EDIT - Removed "COUNT()" function
SELECT [zData Navy FY15 AFS].[Journal Voucher ID], [zData Navy FY15 AFS].[Journal Voucher ID] AS [Record Count], [zData Navy FY15 AFS].[Table Source] AS [Table Source],
[zData Navy FY15 AFS].[dBranch] AS [dBranch], [zData Navy FY15 AFS].[DB_ID] AS [DB_ID], [zData Navy FY15 AFS].[Source] AS [Source], [zData Navy FY15 AFS].[GF/WCF] AS [GF/WCF],
[zData Navy FY15 AFS].[Line of Accounting] AS [Line of Accounting], [zData Navy FY15 AFS].[Accounting Period] AS [Accounting Period],
[zData Navy FY15 AFS].[Beginning/Ending Indicator] AS [Beginning/Ending Indicator], [zData Navy FY15 AFS].[Reason] AS [Reason],
[zData Navy FY15 AFS].[DoDFMR Journal Voucher Category] AS [DoDFMR Journal Voucher Category], [zData Navy FY15 AFS].[Temporary Indicator] AS [Temporary Indicator],
[zData Navy FY15 AFS].[Creator User ID] AS [Creator User ID], [zData Navy FY15 AFS].[Approval User ID] AS [Approval User ID],
[zData Navy FY15 AFS].[Created Date] AS [Created Date], [zData Navy FY15 AFS].[Approval Date] AS [Approval Date],
[zData Navy FY15 AFS].[Unsupported Indicator] AS [Unsupported Indicator], [zData Navy FY15 AFS].[General Ledger Account Code] AS [General Ledger Account Code],
[zData Navy FY15 AFS].[SUS ID] AS [SUS ID], [zData Navy FY15 AFS].[Partner Code] AS [Partner Code], [zData Navy FY15 AFS].[Debit Amount] AS [Debit Amount],
[zData Navy FY15 AFS].[Credit Amount] AS [Credit Amount], [zData Navy FY15 AFS].[Tie Points IND] AS [Tie Points IND],
[zData Navy FY15 AFS].[Auto/Manual] AS [Auto/Manual], [zData Navy FY15 AFS].[Quarter] AS [Quarter], [zData Navy FY15 AFS].[FY] AS [FY],
[zData Navy FY15 AFS].[Record Use] AS [Record Use], [zData Navy FY15 AFS].[Eliminations IND] AS [Eliminations IND],
[zData Navy FY15 AFS].[Fund Number] AS [Fund Number], Len([zData Navy FY15 AFS].[Reason])) AS [Length]
FROM [zData Navy FY15 AFS] Where (False)
UNION ALL