Refernce to cell in SQL statemen

E

Eewo

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(Array( _
"ODBC;DSN=DATMAX;ServerName=SERVER.1583;ServerDSN=DATMAX;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=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
 
B

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top