ASP - Date/Calendar query

Author

Date/Calendar query

2005-08-25, 7:56 am

Hi all,

I have an events database (Access) that contains 2 fields, StartDate and
EndDate. The fields are set as Date/Time long date. The server is UK
date format (dd/mm/yyyy)

I have a small calendar that changes the day of the month to a link if
an event exists for that day.

I start off on day 1 of the month and use a loop through the month until
the last day. Each run through the month queries the DB to see if an
events exists with this sql:

sql="SELECT ListingsID FROM tblEventsListings WHERE StartDate <= #" &
DispDate & "# AND EndDate >= #" & DispDate & "#;"

The DispDate is generated as part of the loop and is producing the date
correctly.

My problem is that if an event has the same start/finish day (single day
event) then it gets missed. Also, an event that runs over a couple of
months seems to randomly be displayed. eg, the calendar will show an
event exists for 4 days, then 5 days of no event then events exist for
the rest of the month. (The whole month should reflect an event on every
day.)

Sorry if this is a bit long winded! I'm stumped! Is there a better way
to do this loop?

Simon

Evertjan.

2005-08-25, 7:56 am

Si wrote on 25 aug 2005 in microsoft.public.inetserver.asp.general:

> I have an events database (Access) that contains 2 fields, StartDate and
> EndDate. The fields are set as Date/Time long date. The server is UK
> date format (dd/mm/yyyy)
>
> I have a small calendar that changes the day of the month to a link if
> an event exists for that day.
>
> I start off on day 1 of the month and use a loop through the month until
> the last day. Each run through the month queries the DB to see if an
> events exists with this sql:
>
> sql="SELECT ListingsID FROM tblEventsListings WHERE StartDate <= #" &
> DispDate & "# AND EndDate >= #" & DispDate & "#;"
>
> The DispDate is generated as part of the loop and is producing the date
> correctly.
>
> My problem is that if an event has the same start/finish day (single day
> event) then it gets missed. Also, an event that runs over a couple of
> months seems to randomly be displayed. eg, the calendar will show an
> event exists for 4 days, then 5 days of no event then events exist for
> the rest of the month. (The whole month should reflect an event on every
> day.)
>
> Sorry if this is a bit long winded! I'm stumped! Is there a better way
> to do this loop?
>

Date values include the time of day,
so 2005/08/25 13:00 <= 2005/08/25 gives false,
while 2005/08/25 13:00 >= 2005/08/25 gives true.

Does this solve your problem?

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Bob Barrows [MVP]

2005-08-25, 6:55 pm

Si wrote:
> Hi all,
>
> I have an events database (Access) that contains 2 fields, StartDate
> and EndDate. The fields are set as Date/Time long date. The server is
> UK date format (dd/mm/yyyy)

Not relevant. Access, along with most other database products, does not
store date/times with any format. Jet (Access) stores dates as Double
numbers, with the whole number portion representing the number of days from
the seed date (I forget what seed date Jet uses - you can find out by
running this query: Select CDate(0) ), and the decimal portion representing
the time of day (.5 = noon). It is the client application that retrieves the
dates from Jet that applies the formatting (and yes, Access itself is a
client application using the Jet database as its backend).

>
> I have a small calendar that changes the day of the month to a link if
> an event exists for that day.
>
> I start off on day 1 of the month and use a loop through the month
> until the last day. Each run through the month queries the DB to see
> if an events exists with this sql:
>
> sql="SELECT ListingsID FROM tblEventsListings WHERE StartDate <= #" &
> DispDate & "# AND EndDate >= #" & DispDate & "#;"
>
http://www.aspfaq.com/show.asp?id=2040

Bob barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Sponsored Links