Access 97 and DateAdd() function in stored procedure

[Matte Edens]

I'm trying to retrieve any rows added in the past week and using the Query Builder in acc97 gave me this...

SELECT quickLists.quickListID, quickLists.listTitle, quickLists.listSource, quickLists.numItems
FROM quickLists
WHERE (((quickLists.dateCreated)>DateAdd("ww",-1,Now())));

The error that appears when I display my ASP page is this ...
---------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access 97 Driver] Unknown function name in query expression '(((quickLists.dateCreated)>DateAdd("ww&#3 4;,-1,Now())))'.
---------

Am I just not able to use the DateAdd function in a query of this type? And if not, why is it provided for in the querybuilder? And, one last question, what would be a solution to what I need to do?

Matte Edens
Web Producer - KNTV NewsChannel 11 / KBWB WB20
webmaster@kntv.com

[Tim Brooks]

Matte,

I can't say I know excatly what you are trying to do. I'll cover two possibilities.

1) You're trying to pull records where quickLists.dateCreated > Current date.
I wouldn't do it your way. Before your recordset I'd create a variable, let's call it dToday, which I'd set equal to the current date using the
Date() function -- you could also use Now() for the comparison you're using, Now gives 'Date + Time' and 'Date' gives the date in 'dd/mm/yy' format.

So..it looks like this:

Dim dDate
dDate = Date()

....some ASP db stuff
MySQL = "SELECT quickLists.quickListID, quickLists.listTitle, quickLists.listSource, quickLists.numItems FROM quickLists WHERE (((quickLists.dateCreated) > " & dDate

OK??

2) You want to do an insert of the current date when you create the record?(Less likely that this is what you want)
But...if so...you could simply make the default value--within Access--equal to
...you guessed it, Date(). I'd use Date$() instead though. Again, this is the defalt value set, inside the Access design view, for the field.

Much luck...
Tim


------------
Matte Edens at 8/5/99 7:39:12 PM


I'm trying to retrieve any rows added in the past week and using the Query Builder in acc97 gave me this...

SELECT quickLists.quickListID, quickLists.listTitle, quickLists.listSource, quickLists.numItems
FROM quickLists
WHERE (((quickLists.dateCreated)>DateAdd("ww",-1,Now())));

The error that appears when I display my ASP page is this ...
---------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access 97 Driver] Unknown function name in query expression '(((quickLists.dateCreated)>DateAdd("ww&#3 4;,-1,Now())))'.
---------

Am I just not able to use the DateAdd function in a query of this type? And if not, why is it provided for in the querybuilder? And, one last question, what would be a solution to what I need to do?

Matte Edens
Web Producer - KNTV NewsChannel 11 / KBWB WB20
webmaster@kntv.com