Date/Calendar query

[Si]

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.]

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?

 

[Bob Barrows [MVP]]

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