Not able to declare variable in MS Query.

  • Thread starter Thread starter Heera
  • Start date Start date
H

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
 
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$"
 
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.
 
Back
Top