| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
| HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
| database query not showing foxpro database How I import data | New Users to Excel | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Using Excel as a database and need macro or vba to take data entered on one tab and update the database by adding to the next avail row | Excel Programming | |||
Linear Mode
