Towe closing parenbthesis where in the wrong spot and you may need the two
single quotes. Below is code that wiull make your original code that worked
equivalent to new code with two variables. I broke the original strings into
multiple strings so I wouldn't get an error in posting the code (lines too
long) and to make it easier to see the individual pieces of the command lines.

comcode = "B08"
transdate = 200812

Columns("A:D").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")


"Fraz " wrote:

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:D").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABOD BC;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:D").Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABOD BC;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


"Mike" wrote:

This passes a startdate and enddate that is stored in A1 and B1
Private Sub getDataFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String, strConn
Dim iCol As Long
Dim sStartDate, sEndDate As String

sStartDate = "#" & Range("A1").Value & "#"
sEndDate = "#" & Range("B1").Value & "#"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Ilsa\data\ilsa.mdb;Persist Security Info=False"

sSQL = "SELECT Sum(RegReconcile.Z_PAYOUTS_QTY) AS SumOfZ_PAYOUTS_QTY, " _
& "Sum(RegReconcile.CSHR_PAYOUTS) AS SumOfCSHR_PAYOUTS FROM
RegReconcile " _
& "INNER JOIN RegZCnt ON RegReconcile.REG_Z_COUNTER =
RegZCnt.REG_Z_COUNTER " _
& "WHERE (((RegZCnt.REG_Z_DATETIME)=" & sStartDate & " And
(RegZCnt.REG_Z_DATETIME)<=" & sEndDate & "));"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
iCol = 1
For Each fld In rs.Fields
Cells(2, iCol).Value = fld.Name
iCol = iCol + 1
Next fld
Cells(3, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub