Hi group,

I'm a newbie to this forum, and to a lesser extent to UltraDev 4 (four months). I use it at my workplace, creating the Web site in ASP/VBScript using UD4, with Access databases as the data source. Whilst I have a basic understanding of ASP/VBScript, it's not enough for me to be able to code manually using it. Please could someone help me with this problem?

I need to create a page which will display database records entered within the past thirty days of the current date - basically, a "What's New" or "latest reports" page. I can set up the display part of the page, no problem - it's the recordset that I can't get to work (or even work out how it's formed).

In theory, I think the recordset (in SQL) would be something like:

SELECT *
FROM Report_Table
WHERE Date BETWEEN #XXX# and #[XXX minus 30 days]#

(XXX is today's date.) I have successfully used the Date() function to add the current date in an insert form, as Macromedia's UD support site tells you how to do this. However, I've been unable to find out how to perform this kind of "dynamic" date search anywhere - does anyone here know how I could put the recordset together?

Many thanks for any help you can give!

All the best,
Tim.


---
Tim Walker - London, U.K.
---

Try something like this:

SELECT *
FROM Report_Table
WHERE YourDateField >= "#" & DateAdd("d", -30, Now())& "#" AND YourDateField <= "#" & Now() & "#"

<b>NOTE - Date is a reserved word, so don't use it as a field name in a database.</b>

Regards

Owen.

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

Edited by - oeastwick on 11/20/2001 17:56:43

Thanks Owen - I'll give this a try, and I'll also take a look at your tutorials page <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Tim

---
Tim Walker - London, U.K.
---

I tried the recordset above, but haven't had much luck with it so far; that's probably more due to my relative ignorance of ASP/VBScript/UD4 than anything else... Is the code meant to be entered directly into the code window, or can it go into the advanced recordset screen?

<i>On a related note:</i>

I'm trying to create a UD4 recordset which will display the records with the <b>current date only</b> - it's for a "Today's Courses" view on the Web site for the courses that we run. The SQL for this query, according to Access 97, is:

SELECT *
FROM Courses
WHERE (((Courses.COURSEDATE)=Date()))
ORDER BY Courses.Title

In other words, "get the records with today's date, and sort them in alphabetical order". Works perfectly in Access, but UD4 will not work with it, no matter how I try to rewrite the SQL. I tried:

SELECT *
FROM Courses
WHERE COURSEDATE = #Date()#
ORDER BY Title
<font color=red>("data mismatch" - COURSEDATE is set to "Date/Time" in Access, so why?)</font id=red>

SELECT *
FROM Courses
WHERE COURSEDATE = #varDateToday#
ORDER BY Title
<font color=blue>varDateToday = default value: 01/01/02; value: Date()</font id=blue>
<font color=red>(UD4 seems to ignore "Date()" completely - should I put "Request("Date()" there, or something?)</font id=red>

Add to the wishlist for UD5, the capability to use/import Access-generated SQL for UD recordsets! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> In the meantime, can anyone please tell me what I'm getting wrong?

Thanks again for your help...

Tim.


---
Tim Walker - London, U.K.
---

You'll have to do this in CodeView on the page, something like:

YourRecordsetName.Source = "SELECT * FROM Courses WHERE CourseDate = " & Date()

Bear in mind that Date Formats will have to match.

so you may have to re-format the date given by Date() to match the format within your database, something like:

&lt;%
varLocale = SetLocale(1031)
varDate = Now()
varDate = FormatDateTime(vardate, 0)
%&gt;

Then set up the recordset:

YourRecordsetName.Source = "SELECT * FROM Courses WHERE CourseDate = " & varDate

I've answered dozens of date formating questions on this forum, search for the following keywords:

FormatDateTime - SetLocale - LCID

Should find some useful posts.

Take a look at this post as well:

www.udzone.com/forum/topic.asp?TOPIC_ID=12591&FORUM_ID=13&CAT_ID=5&Topic_Title=SELECT+WHERE+DATE+%3F&Forum_Title=SQL

Regards

Owen.

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