Plse ignore my previous post as I sent it before I saw your reply below. I
can see that your code is much tidier than what I had and therefore is much
clearer to follow, somthing I've learnt for the future.

I'm still getting a runtime error 13, mismatch against the following bit of
code;

..CommandText = Array(, SelectSQL & vbCrLf & _
FromSQL & vbCrLf & WhereSQL & vbCrLf & GroupbySQL)

Thanks for all of your help on this



"joel" wrote:


I had some time. This is how I setup complicated Queries. You need to
place Carriage Returns and Linefeed every 256 characters (I used
vbcrlf).




VBA Code:
--------------------



Sub Macro1()
'

Set Sht = Sheets("sheet1")
AccountNumber = "Customer NBR"
CompanyCode = "950"
LedgerSection = "Business Area"
MonthEndPeriod = "201003"



SelectSQL = "SELECT A_Customer_Month_End_View_UK.""Ledger Section""," & _
"A_Customer_Month_End_View_UK.""Account Number""," & _
"A_Customer_Month_End_View_UK.""Customer Name""," & _
"A_Customer_Month_End_View_UK.""Credit Limit""," & _
"A_Customer_Month_End_View_UK.""Balance""," & vbCrLf & _
"A_Customer_Month_End_View_UK.""OverDUe"" AS 'Overdue'," & _
"A_Customer_Month_End_View_UK.""Not Yet Due""," & _
"A_Customer_Month_End_View_UK.""Falling Due""," & _
"A_Customer_Month_End_View_UK.""Past Due 1""," & _
"A_Customer_Month_End_View_UK.""Past Due 2""," & _
"A_Customer_Month_End_View_UK.""Past Due 3""," & _
"A_Customer_Month_End_View_UK.""Past Due 4""," & _
"A_Customer_Month_End_View_UK.""Past Due 5""," & vbCrLf & _
"A_Customer_Month_End_View_UK.""Unallocated"", " & _
"A_Customer_Month_End_View_UK.""In Query""," & _
"A_Customer_Month_End_View_UK.""Forward Dated""," & _
"Sum(A_Open_Items_Month_End_View_UK.amount) AS 'Sum of Amount'," & _
"_ , _ A_Open_Items_Month_End_View_UK.""Report Fiscal Date""," & _
"Count(A_Open_Items_Month_End_View_UK.Query) AS 'Count of Query'"

FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK A_Customer_Month_End_View_UK"

WhereSQL1 = "A_Customer_Month_End_View_UK.""Account Number"" ='" & AccountNumber & "'"
WhereSQL2 = "A_Customer_Month_End_View_UK.""Company Code"" ='" & CompanyCode & "'"
WhereSQL3 = "A_Customer_Month_End_View_UK.""Ledger Section"" ='" & LedgerSection & "'"
WhereSQL4 = "A_Customer_Month_End_View_UK.""Month End Period"" ='" & MonthEndPeriod & "'"
WhereSQL = "Where " & WhereSQL1 & " AND " & WhereSQL2 & " AND " & WhereSQL3 & " AND " & WhereSQL4

GroupBy1 = "A_Customer_Month_End_View_UK.""Ledger Section"","
GroupBy2 = "A_Customer_Month_End_View_UK.""Account Number"","
GroupBy3 = "A_Customer_Month_End_View_UK.""Customer Name"","
GroupBy4 = "A_Customer_Month_End_View_UK.""Credit Limit"","
GroupBy5 = "A_Customer_Month_End_View_UK.""Balance

GroupbySQL = "GROUP BY " & GroupBy1 & GroupBy2 & GroupBy3 & _
GroupBy4 & GroupBy5

With Sht
.Cells.RemoveSubtotal
With .QueryTables.Add(Connection:="ODBC;DRIVER=SQLServe r;" & _
"SERVER=usoldt-as-056;UID=dms_uk;;" & _
"APP=Microsoft Office 2003;WSID=UKEDG-L41655;" & _
"DATABASE=dms_reporting", _
Destination:=.Range("A5"))

.CommandText = Array(, SelectSQL & vbCrLf & _
FromSQL & vbCrLf & WhereSQL & vbCrLf & GroupbySQL)

.Name = "Query from DMS UK Production Month End"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End With
End Sub


--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198559

http://www.thecodecage.com/forumz

.