Im having trouble creating a recordset that will list the products that have been added or updated within the last X days.

Im working with generic Date() stamp.

Any help?

well, I think you should be able to use the "dateadd" function to help you out here... the sql statement should look something like this:<pre id=code><font face=courier size=2 id=code>SELECT * FROM thetable WHERE datestamp &gt;= 'mm_Date' ODER BY datestamp DESC</font id=code></pre id=code>
mm_Date = dateadd("dd",-5,date)

this would set the mm_Date variable to 5 days prior to today (note: double check syntax of dateadd... it might be wrong)

that should give you theresults you want.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
well, I think you should be able to use the "dateadd" function to help you out here... the sql statement should look something like this:<pre id=code><font face=courier size=2 id=code>SELECT * FROM thetable WHERE datestamp &gt;= 'mm_Date' ODER BY datestamp DESC</font id=code></pre id=code>
mm_Date = dateadd("dd",-5,date)

this would set the mm_Date variable to 5 days prior to today (note: double check syntax of dateadd... it might be wrong)
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

erm, i dont think its wrong in theory ... but it doesnt work - or perhaps im not doing it right.

what im doing is

<pre id=code><font face=courier size=2 id=code>
SELECT *
FROM thetable
WHERE datestampfield &gt;= 'mm_Date'
ORDER BY datestampfield DESC</font id=code></pre id=code>

and then
[/code]mm_Date = dateadd("d",-5,now)[/code]

(which i put in the critera)

This isnt working ... Ive been inserting this code into the recordset dialog, except im getting datatype mismatch errors ...

can you see anything wrong with this?

I think the problem is that you are looking for mm_Date as a text string. Also you need to define the variable mm_Date brfore using it as part of the WHERE clause.

First:
mm_Date = dateadd("d",-5,now())

Then:
SELECT * FROM thetable WHERE datestampfield &gt;= mm_Date ORDER BY datestampfield DESC

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

ok, im still not getting this. Im getting new errors. In my advanced recordset dialog box I have:

SELECT *
FROM qryProducts
WHERE productLastupdate &gt;= mm_Date
ORDER BY productLastUpdate DESC

and then where i declare the variables I have:

Name
mm_date

Default Value:
dateadd("d",-5,now())

Runtime Value:
dateadd("d",-5,now())

and my SQL looks like this:

&lt;%
Dim rsLatest__mm_Date
rsLatest__mm_Date = "dateadd("d",-5,now())"
if (dateadd("d",-5,now()) &lt;&gt; "" then rsLatest__mm_Date = dateadd("d",-5,now())
%&gt;
&lt;%
set rsLatest = Server.CreateObject("ADODB.Recordset"
rsLatest.ActiveConnection = MM_connUltraGo_STRING
rsLatest.Source = "SELECT * FROM qryProducts WHERE productLastupdate &gt;= " + Replace(rsLatest__mm_Date, "'", "''" + " ORDER BY productLastUpdate DESC"
rsLatest.CursorType = 0
rsLatest.CursorLocation = 2
rsLatest.LockType = 3
rsLatest.Open()
rsLatest_numRows = 0
%&gt;

All this works fine when i do a test in the recordset and I was delighted when it worked.

Except now when I test the page im getting this error:

Expected end of statement
/esquared/latestupdate.asp, line 11
rsLatest__mm_Date = "dateadd("d",-5,now())"
------------------------------^

This is driving me insane, I hate these damn annoying bugs that cant be resolved.

In the code view try this:

GET RID OF THIS:
&lt;%
Dim rsLatest__mm_Date
rsLatest__mm_Date = "dateadd("d",-5,now())"
if (dateadd("d",-5,now()) &lt;&gt; "" then rsLatest__mm_Date = dateadd("d",-5,now())
%&gt;

THEN MODIFY THIS LINE, FROM THIS:

rsLatest.Source = "SELECT * FROM qryProducts WHERE productLastupdate &gt;= " + Replace(rsLatest__mm_Date, "'", "''" + " ORDER BY productLastUpdate DESC"

TO THIS:

rsLatest.Source = "SELECT * FROM qryProducts WHERE productLastupdate &gt;= " & DateAdd("d",-5,Now()) & " ORDER BY productLastUpdate DESC"

Also som things to bear in mind.

What format is the date field "productLastupdate" in your database? mm/dd/yyyy or dd/mm/yyyy etc.

Check that DateAdd("d",-5,Now()) is providing a date in a similar format, you can do this by just displaying it on a test page: &lt;% = DateAdd("d",-5,Now()) %&gt;

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thanks for that ... the code works, sorta.

user.7host.com/esquared/latestupdate.asp

Im getting ALL records now. deep sigh.

The test works, the dates uses mm/dd/yyyy &lt;BTW, working with Date() rather than Now()&gt;

I cant see a reason why its doing that except perhaps that its not reading the dateadd function.

Repeat regions wouldnt have an effect would they?