Trying to use serialdate in database array

[Brian]

Howdy all,

I have this line in a macro:

..CommandText = Array( _
"SELECT `Combined Data`.`Assignee Mgr Name`, `Combined Data`.`SR
Reported Date`" & Chr(13) & "" & Chr(10) & "FROM `Combined Data` `Combined
Data`" & Chr(13) & "" & Chr(10) & "WHERE (`Combined Data`.`Assignee Mgr
Name`='SMITH, GEORGE') AND (`Combi" _
, _
"ned Data`.`SR Reported Date`>={ts '2008-01-01 00:00:01'})" &
Chr(13) &
"" & Chr(10) & "ORDER BY `Combined Data`.`SR Reported Date`" _
)

What I want to change is the date ('2008-01-01 00:00:01') to something like
this:

serialdate(year(now)-1, month(now), day(now))

In order to have the results return be from today's date to one year
previous.
When I substitute my line in the code, I get a 1004 error at this line:

..Refresh BackgroundQuery:=False

Any ideas?

Thanks,
Brian

 

[joel]

MyDate = serialdate(year(now)-1, month(now), day(now))

"SELECT `Combined Data`.`Assignee Mgr Name`, `Combined Data`.`SR
Reported Date`" & Chr(13) & "" & Chr(10) & "FROM `Combined Data` `Combined
Data`" & Chr(13) & "" & Chr(10) & "WHERE (`Combined Data`.`Assignee Mgr
Name`='SMITH, GEORGE') AND (`Combi" _
, _
"ned Data`.`SR Reported Date`>={ts '" & MyDate & "'})" &
Chr(13) &
"" & Chr(10) & "ORDER BY `Combined Data`.`SR Reported Date`" _
)

 

[Brian]

Sorry for my dyslexia, I mean dateserial, which is what I have in my code.

Joel,

I still get a this error:

Run-time error '1004':
SQL Syntax Error

Any ideas?

 

[joel]

I think it is looking for text with a "-" between the year-month-day

MyDate = DateSerial(Year(Now) - 1, Month(Now), Day(Now))
MyDate = Format(MyDate, "YYYY-MM-DD HH:MM:SS")

 

[Brian]

Thanks Joel, you were correct.

Works like a charm.

Brian