MX/ASP/Access - Repeat Region by Date 1

[uridium]

Hi,

I'm putting together an ASP/Access based website using DWMX (not 2004). I'm fairly familiar with the server behaviours, but not so confident about diving into the code head-first (although I do know the basics)

Basically, what I want to do is create a repeated region that spans over a set of dates, eg the next seven days. Now, as it stands, the repeated region behaviour only lets me show either the next x number of records or all of them.

Does anybody know how I might solve this problem, or is there a tutorial someone can point me towards?

Thanks in advance,


--
Olly

http://planetgnarly.com

 

[Cheech]

Only pull the records that fall between the dates required

assign the dates to select on:
strFirstDate = date()
strLastDate = DateAdd("d", 7, strFirstDate)

Your sql statement will be something like:

"SELECT *  FROM tblYourTable WHERE fldYourdateField BETWEEN #" & strFirstDate & "# AND #" & strLastDate & "# ORDER BY fldYourdateField ASC"

Be aware though that Access has some very funny ways of looking at dates. I use a bit of script first like this.

strFirstDate = Date()
dd=DatePart("d", strFirstDate)
if len(dd)=1 then dd= "0" & dd end if
mm=DatePart("m", strFirstDate)
if len(mm)=1 then mm= "0" & mm end if
yy=DatePart("yyyy", strFirstDate)
if len(yy)=2 then yy= "20" & yy end if
strFirstDate = dd & "/" & mm & "/" & yy


strLastDate = Date()
dd=DatePart("d", strLastDate)
if len(dd)=1 then dd= "0" & dd end if
mm=DatePart("m", strLastDate)
if len(mm)=1 then mm= "0" & mm end if
yy=DatePart("yyyy", strLastDate)
if len(yy)=2 then yy= "20" & yy end if
strLastDate = dd & "/" & mm & "/" & yy

That would go in the head of your document wrapped in

<% %>

tags and the select would just replace the select statement you have with the table and field names changed to reflect your own.

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[Cheech]

oops

strLastDate = DateAdd("d", 7, strFirstDate)
instead of
strLastDate = Date()

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

Thats absolutely brilliant, thank you

I thought of doing it from the SQL after I posted the question but I just couldn't come up with the query.

 

[uridium]

OK, perhaps its not so great after all

It appears to be picking up records upto and including strFirstDate, but nothing after that date. Any ideas?

--
Olly

http://planetgnarly.com/

 

[Cheech]

Hey Olly,

Maybe try this:

&quot;SELECT *  FROM tblYourTable WHERE fldYourdateField >= #&quot; & strFirstDate & &quot;# AND < #&quot; & strLastDate & &quot;# ORDER BY fldYourdateField ASC&quot;

But that code works for me on an access database.

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

Mmm, as far as I can tell, your original code should work fine. Here's my version:

futureEvents.Source = "SELECT * FROM Entries WHERE entry_type = 'Events' AND event_dayt BETWEEN #" & DateValue(strFirstDate) & "# AND #" & DateValue(strLastDate) & "# ORDER BY event_dayt ASC"

It returns the same thing with or without DateValue() around the date variables - that is data upto and including strFirstDate, but nowt after it.

Your second snippet (subtly altered to include another fldYourdateField after the AND) returns nothing at all, which strikes me as ever so slightly odd.

--
Olly

http://planetgnarly.com/

 

[Cheech]

what do you have the fields set to in the database? Mine are short date

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

Data type is Date/Time, Format is Short Date.

I'm sure i'll kick myself when I figure this one out.

--
Olly

http://planetgnarly.com/

 

[uridium]

It wouldn't be something to do with date formats would it? I'm using DD/MM/YYYY - should it be MM/DD/YYYY or something?



--
Olly

http://planetgnarly.com/

 

[Cheech]

You have to be very careful with date formats esp in Access. This is why I use the script at the top of the page to format it how I want. i then use that on every instance of a date in the pages including the insert page that puts the events into the database.

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

OK, starting to make some progress now. strLastDate seems to be the problem.

WHERE fldYourdateField >= #" & strFirstDate & "#

Works fine - it returns everything after strFirstDate as expected.

WHERE fldYourdateField < #&quot; & strLastDate & &quot;#

Is borken. It returns nothing at all. Any ideas?

--
Olly

http://planetgnarly.com/

 

[uridium]

Right, more progress.

If strLastDate falls into next month the whole thing falls apart, otherwise it works fine. Mumblegrumble.

--
Olly

http://planetgnarly.com/

 

[Cheech]

Stick these 3 lines just after your select statement is declared

response.write(rsRecordset.Source)
response.write(strFirstDate)
response.write(strLastDate)

Too see exactly what dates are being used and what is being asked of the database and post the results here.

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

SELECT * FROM Entries WHERE event_dayt BETWEEN #01/12/2003# AND #08/12/2003# ORDER BY event_dayt ASC

01/12/2003

08/12/2003

Which is returning nothing, even though there are records where event_dayt = 03/12/2003


Something screwy is going on, because if I change it to:

SELECT * FROM Entries WHERE event_dayt >= #01/12/2003# ORDER BY event_dayt ASC

it returns all records regardless of event_dayt (ranging from 20/11/2003 to 03/12/2003).

--
Olly

http://planetgnarly.com/

 

[Cheech]

I hate doing this, as forums are meant for discussion, but can you email a copy of your page and database in a zip to the_real_cheech@hotmail.com

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[uridium]

Its alright, its sorted now

It turned out to be a date formats problem.

I had to switch the dates (strFirstDate etc) around from DD/MM/YYYY to MM/DD/YYYY to make it work.

Thanks for your help anyway

--
Olly

http://planetgnarly.com/