Refernce to cell in SQL statemen
 

Hi all,

I have a problem. I recorded Macro that conects to ODBC database and pulls
some data to Excel sheet. I would like that user puts date in a cell and
that this date is passed to query. My macro looks like this:

Sub Makronaredba1()

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=DATMAX;ServerName=SERVER.1583;ServerDSN= DATMAX;ArrayFetchOn=1;ArrayBufferSize=8;TransportH int=TCP:SPX;DecimalSymbol=,;Clien"
_
),
Array("tVersion=8.50.189.000;CodePageConvert=1250; AutoDoubleQuote=0;")), _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ""Transaction History"".PRTNUM_15, ""Transaction
History"".TNXDTE_15" & Chr(13) & "" & Chr(10) & "FROM ""Transaction
History"" ""Transaction History""" & Chr(13) & "" & Chr(10) & "WHERE
(""Transaction History"".TNXDTE_15={d '2006-05-04'} And ""Transaction His"
_
, _
"tory"".TNXDTE_15<={d '2009-01-01'})" & Chr(13) & "" & Chr(10) &
"ORDER BY ""Transaction History"".PRTNUM_15" _
)
.Name = "Query from DATMAX"
.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 Sub

I would like to put cell reference in this part of code:

"WHERE (""Transaction History"".TNXDTE_15={d '2006-05-04'}

So that insted '2006-05-04' is something like Range(A1) or something like
this (also with reference to sheet).

I hope that someone can help me

Eewo

Refernce to cell in SQL statemen
 

"WHERE
(""Transaction History"".TNXDTE_15={d '" & Format(Range("A1").value,
"yyyy-mm-dd") & "'} And ""Transaction His"


--
__________________________________
HTH

Bob

"Eewo" wrote in message
...
Hi all,

I have a problem. I recorded Macro that conects to ODBC database and pulls
some data to Excel sheet. I would like that user puts date in a cell and
that this date is passed to query. My macro looks like this:

Sub Makronaredba1()

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=DATMAX;ServerName=SERVER.1583;ServerDSN= DATMAX;ArrayFetchOn=1;ArrayBufferSize=8;TransportH int=TCP:SPX;DecimalSymbol=,;Clien"
_
),
Array("tVersion=8.50.189.000;CodePageConvert=1250; AutoDoubleQuote=0;")), _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ""Transaction History"".PRTNUM_15, ""Transaction
History"".TNXDTE_15" & Chr(13) & "" & Chr(10) & "FROM ""Transaction
History"" ""Transaction History""" & Chr(13) & "" & Chr(10) & "WHERE
(""Transaction History"".TNXDTE_15={d '2006-05-04'} And ""Transaction
His" _
, _
"tory"".TNXDTE_15<={d '2009-01-01'})" & Chr(13) & "" & Chr(10) &
"ORDER BY ""Transaction History"".PRTNUM_15" _
)
.Name = "Query from DATMAX"
.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 Sub

I would like to put cell reference in this part of code:

"WHERE (""Transaction History"".TNXDTE_15={d '2006-05-04'}

So that insted '2006-05-04' is something like Range(A1) or something like
this (also with reference to sheet).

I hope that someone can help me

Eewo