Hi Mike, I used some of the parts of your code with my code, However i get
the error SQL Syntex error, Error 1004. and it highlights in yellow the very
last line of the code { .Refresh BackgroundQuery:=True}
I am not sure what I am doing wrong. If instead of using variable parameters
i use fixed parameteres the macro works fine. the following are two macros
WITH FIXED PARAMETERS (WORKS FINE)
Sub B08DATAGL_Query()
' MACRO TO RUN WITH FIXED CRITERIA WORKS JUST FINE
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
'
Columns("A
").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC",
Destination _
:=Range("A3"))
.CommandText = Array( _
"SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO,
GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM
SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE
(GLACC_PER.COM_CODE= 'B08') AND (GLACC_PER.PERIOD_NO<=200812) AND (GLACC_P" _
, "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY
GLACC_PER.PERIOD_NO DESC")
.Name = "DATAGLQUERY1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
WITH VARIABLE PARAMETERES GIVES ERRORS.
Sub B08DATAGL_Query_VARIABLE()
' MACRO TO RUN WITH VARIABLE CRITERIA DOES NOT WORK
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
Dim comcode As Variant
Dim transdate As Date
comcode = ActiveSheet.Range("E2").Text
transdate = ActiveSheet.Range("F2").Value
Columns("A
").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC",
Destination _
:=Range("A3"))
.CommandText = Array( _
"SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO,
GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM
SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE
(((GLACC_PER.COM_CODE)=" & comcode & " AND (GLACC_PER.PERIOD_NO)<=" &
transdate & " AND (GLACC_P" _
, "ER.CLOSE_BAL<>0)));" & Chr(13) & "" & Chr(10) & "ORDER BY
GLACC_PER.PERIOD_NO DESC")
.Name = "DATAGLQUERY1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
Your help will be much appreciated..
thanks (e-mail address removed)