Date/Calendar query

Discussion in 'ASP General' started by Si, Aug 25, 2005.

  1. Si

    Si Guest

    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
     
    Si, Aug 25, 2005
    #1
    1. Advertising

  2. Si

    Evertjan. Guest

    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)
     
    Evertjan., Aug 25, 2005
    #2
    1. Advertising

  3. 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.
     
    Bob Barrows [MVP], Aug 25, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shevek
    Replies:
    3
    Views:
    6,103
    Shevek
    Jun 23, 2004
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,333
    Michael Borgwardt
    May 30, 2004
  3. Nithin

    Calendar control as Date Query problem.

    Nithin, Jan 22, 2004, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    139
  4. Shevek
    Replies:
    0
    Views:
    417
    Shevek
    Jun 23, 2004
  5. Replies:
    0
    Views:
    206
Loading...

Share This Page