can you use a "where" in an excel macro select statement

[chainedtodesk]

i have a macro that pulls data from a specific access table, i do not want to add more updates to the access DB but would instead like to update the macro. the file has now accumulated two years worth of data and the grid is very cluttered so i would like to only pull the current year, but no matter what i do to the code it will not work when i add a "WHERE" clause. can this be done or am i making more changes to the access DB to solve this?? thanks...

code snipit:

'CS_AVGS query
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\mine\data\Database\Active_DB_BackEnds\CS.accdb;DefaultDir=\\mine\data\Database\Active_DB_BackEnds;DriverId=25;FIL=MS Acc" _
), Array("ess;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1") _
).QueryTable
.CommandText = Array( _
[highlight #FCE94F] "SELECT `CS_AVGS`.entryID, `CS_AVGS`.end, `CS_AVGS`.CountOfopen, `CS_AVGS`.AvgOfOpenDays, `CS_AVGS`.CountOfclosed, `CS_AVGS`.AvgOfClsddays" & Chr(13) & "" & Chr(10) & "FROM `\\mine\data\Database\Active_DB_BackEnds\CS.accdb`.`CS_AVGS` `CS_AVGS`" & Chr(13) & "" & Chr(10) & " where year(`CS_AVGS`.end) = year(date())"[/highlight] _
)
.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_ExternalData_2"
.Refresh BackgroundQuery:=False
End With

 

[combo]

You can't use date() in your sql. Instead, build your sql string and insert year with year(date()) vba function:
...
where year(`CS_AVGS`.end) = " & year(date()))


combo

 

[SkipVought]

Also be advised that each time you run the macro containing the code that ADDS a ListObject, you ER, uh ... ADD a new ListObject object NEEDLESSLT to your sheet.

You rather ought to delete all the ListObjects on you're sheet and MANUALLY add your ListObject.Querytable ONE TIME.

From there you can change the data source and refresh your QueryTable as well all in your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[chainedtodesk]

created a query in access to get all my data buckets as needed and was able to get the process to work, couldnt get where statement to work will try to revisit when this time pressued project is complete. thanks all