SQL UNION ALL problem after using UNION ALL more than 10 times
- by VBGKM
I'm getting a formatting problem if I use more than 10 UNION ALL statements in my VBA Code.
If I use 10 or less everything works great.
What I'm trying to do is combine 12 worksheets (Excel 2007).
I have a numerical column called SC that turns into string and date if I have more than 10 UNION ALL. If I try to use ROUND with more than 10 UNION ALL my last selection will change all the records by one unit.
I'm using Microsoft.ACE.OLEDB.12.0 as my provider and my connection string has worked for several things in my code so far.
Is there any limit for UNION ALL statements when using OLEDB?
Here is my code.
Dim StrOr As String
Dim i As Variant
Dim Cnt As ADODB.Connection
Dim Rs As ADODB.Recordset
For i = 1 To 12
StrOr = StrOr & " " & "SELECT SC FROM [" & MonthName(i, True) & "$" & "] UNION ALL"
Next
StrOr = Left(StrOr, Len(StrOr) - 9) & ";"
Call GetADOCnt
Call ADORs
