Trying to use serialdate in database array
 

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" wrote in message
...
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" wrote:

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

Trying to use serialdate in database array
 

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" wrote:

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" wrote in message
...
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" wrote:

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

Trying to use serialdate in database array
 

Thanks Joel, you were correct.

Works like a charm.

Brian
"joel" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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