Not able to declare variable in MS Query.
 

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_Fi les").TableStyle
= ""
End Sub

Regards
Heera

Not able to declare variable in MS Query.
 

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$"
--
HTH...

Jim Thomlinson


"Heera" wrote:

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_Fi les").TableStyle
= ""
End Sub

Regards
Heera

Not able to declare variable in MS Query.
 

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.