Not being a VBA whizz i recorded a macro which imports data from an external
database and when i run this macro it works great

One of the fields on this database is a date field and i want this query to
only bring in the records based on a date the user would enter in an input box

I've tried using input box and a variable which would hold the date and
refer to this variable in the line
"WHERE (GL_EXTRACT.RECEIVED_DATE={ts '2009-06-10 00:00:00'})" _
)

the problem i'm having is the date is in double quotes which would make the
variable no good.

is it possible to change this code so that the user can enter a date and the
query would bring back records based on that date?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24/06/2009 by User
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=BETN;UID=MRBLOGGS;;DBQ=BETN;DBA=W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;" _
), Array("MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")),
Destination:=Range("A1" _
))
.CommandText = Array( _
"SELECT GL_EXTRACT.EXTRACTN, GL_EXTRACT.RECEIVED_DATE,
GL_EXTRACT.RECORD_COUNT, GL_EXTRACT.ACCOUNT_TYPE, GL_EXTRACT.ACCOUNTN,
GL_EXTRACT.BUS_CLASS, GL_EXTRACT.FUND_CLASS, GL_EXTRACT.CO_CODE, GL_EXTRACT" _
, _
".ORACLE_AC, GL_EXTRACT.INTERFUND, GL_EXTRACT.DAYS_CREDITS,
GL_EXTRACT.DAYS_DEBITS" & Chr(13) & "" & Chr(10) & "FROM UNIWIN.GL_EXTRACT
GL_EXTRACT" & Chr(13) & "" & Chr(10) & "WHERE (GL_EXTRACT.RECEIVED_DATE={ts
'2009-06-10 00:00:00'})" _
)
.Name = "Query from BETN"
.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

thedate = "2009-06-10"


"WHERE (GL_EXTRACT.RECEIVED_DATE={ts '" & thedate & "'})" )



"Mark" <(E-Mail Removed)> wrote in message
news:9D73A7B4-3CDE-4AD5-8D0E-(E-Mail Removed)...
> Not being a VBA whizz i recorded a macro which imports data from an
> external
> database and when i run this macro it works great
>
> One of the fields on this database is a date field and i want this query
> to
> only bring in the records based on a date the user would enter in an input
> box
>
> I've tried using input box and a variable which would hold the date and
> refer to this variable in the line
> "WHERE (GL_EXTRACT.RECEIVED_DATE={ts '2009-06-10 00:00:00'})" _
> )
>
> the problem i'm having is the date is in double quotes which would make
> the
> variable no good.
>
> is it possible to change this code so that the user can enter a date and
> the
> query would bring back records based on that date?
>
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 24/06/2009 by User
> '
>
> '
> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
>
> "ODBC;DSN=BETN;UID=MRBLOGGS;;DBQ=BETN;DBA=W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;"
> _
> ), Array("MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")),
> Destination:=Range("A1" _
> ))
> .CommandText = Array( _
> "SELECT GL_EXTRACT.EXTRACTN, GL_EXTRACT.RECEIVED_DATE,
> GL_EXTRACT.RECORD_COUNT, GL_EXTRACT.ACCOUNT_TYPE, GL_EXTRACT.ACCOUNTN,
> GL_EXTRACT.BUS_CLASS, GL_EXTRACT.FUND_CLASS, GL_EXTRACT.CO_CODE,
> GL_EXTRACT" _
> , _
> ".ORACLE_AC, GL_EXTRACT.INTERFUND, GL_EXTRACT.DAYS_CREDITS,
> GL_EXTRACT.DAYS_DEBITS" & Chr(13) & "" & Chr(10) & "FROM UNIWIN.GL_EXTRACT
> GL_EXTRACT" & Chr(13) & "" & Chr(10) & "WHERE
> (GL_EXTRACT.RECEIVED_DATE={ts
> '2009-06-10 00:00:00'})" _
> )
> .Name = "Query from BETN"
> .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
>