VBA to ms access query date not working

[rambans21]

Hi

im hoping someone can help me with this because i am really struggling.. I am having issue with ".CommandText = Array( _ "SELECT * FROM Consolidated WHERE [Start Date]> '2019-01-01 00:00:00'")"

it seems to be working fine if i do the criteria on brand or anything else.. however it doesnt want to work on date it just comes up with error "application-defined or object-defined error"


I have tried many different formats of dates.


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Meme\AppData\Local\Microsoft\Windows\Tem" _
, _
"porary Internet Files\Content.MSO\AC2EDA4A.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet O" _
, _
"LEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global " _
, _
"Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=Fals" _
, _
"e;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;J" _
, _
"et OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable

.CommandText = Array( _
"SELECT * FROM Consolidated WHERE [Start Date]> '2019-01-01 00:00:00'")



.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"example"
.Refresh BackgroundQuery:=False
End With


Thanks

 

[jkpieterse]

Re: VBA to ms access query date not working.. please help!!

You can omit the Array bit and dates need the be surrounded by the # sign. I also assumed the time is irrelevant so I omitted it:

.CommandText = "SELECT * FROM Consolidated WHERE [Start Date]> #2019-01-01#"