Not able to declare variable in MS Query.

[Heera]

Hi All,

I am seriously in a problem.

I have recorded a macro which pull's out data from a database with the
help of MS query.

The user of this macro will pull the data from a specific date to a
specific date and for that I have applied filter in the query itself.
But my problem comes when I try to fire the query by declaring a
variable.

The error which I get is mentioned below.
Run-time error '1004':
General ODBC Error

I have declared the date as variable. Please help, here is my code.

Sub Macro5()
'
' Macro5 Macro
'

Dim Ddate1 as Date
Dim Ddate2 as Date

Ddate1 = ThisWorkbook.Sheets("Report Manager").Range("C3").Value
Ddate2 = ThisWorkbook.Sheets("Report Manager").Range("C4").Value



With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array
( _
"ODBC;DSN=Excel Files;DBQ=\\10.253.23.5\TPS\TPS_COLLECTIONS
\MIS Report\Data Base\Consolidated Database Of Call
Audits.xlsm;DefaultDir" _
), Array( _
"=\\10.253.23.5\TPS\TPS_COLLECTIONS\MIS Report\Data
Base;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("$A$9")).QueryTable
.CommandText = Array( _
"SELECT `Sheet1$`.`Audit Date`, `Sheet1$`.`Agent Name`,
`Sheet1$`.`F ID`, `Sheet1$`.Evaluator" & Chr(13) & "" & Chr(10) &
"FROM `Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE
(`Sheet1$`.`Audit Date`>={ts Ddate1} And `Sheet1$`.`Audit D" _
, "ate`<={ts Ddate2})" & Chr(13) & "" & Chr(10) & "ORDER BY
`Sheet1$`.`Audit Date`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Excel_Files"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Table_Query_from_Excel_Files").TableStyle
= ""
End Sub

Regards
Heera

 

[Jim Thomlinson]

Your problem is that you are using the text Ddate1 in your query and not the
values associated with the variable Ddate1. Try this to see what I mean. Add
a message box...

msgbox "Audit Date`>={ts Ddate1} And `Sheet1$"
and another message box
msgbox "Audit Date`>={ts " & Ddate1 & "} And `Sheet1$"

 

[Heera]

Jim i did not understood what you are trining to explane me.

And how do i add Message box.

It will be great help for me if modify my query and show me an example.