SQL Question

Results 1 to 4 of 4

Thread: SQL Question

  1. #1

    Default SQL Question

    Hi guys,<BR>I have a question regarding an sql statement. I have an access database that is storing events from an events calendar. My dates are stored as basic date/time fields. <BR><BR>Eg.<BR>eventdate_masterSTART - 31/10/2004 8:00:00 AM<BR>eventdate_masterFINISH - 31/10/2004 11:00:00 AM<BR>ID - Unique Identifier<BR>eventID - Random NO (eg. 97529115686182332661) This is used to group events of which contain more than one event as part of a series.<BR><BR>What I would like to do on my events listing page is display the output to screen like the example shown below.<BR><BR>http://www.ourbrisbane.com/whatson/events/?nav=dd<BR><BR>Im not quite sure how to go about this, and would appreciate some input or suggestions.<BR><BR>Thanks guys,<BR>John

  2. #2

    Default anyone??

    sql = "SELECT brains FROM thinkharder WHERE effort &#060;&#062; &#039;laziness&#039;"<BR><BR>:-)

  3. #3
    Join Date
    Dec 1969

    Default Why is this difficult???

    I&#039;m not sure what you are after.<BR><BR>Oh, wait...I think I see.<BR><BR>You want the user to specify a range of dates and then you show all events anywhere in that range?<BR><BR>Hope so.<BR><BR>Okay, build a table of possibilities. <BR><BR>In this table, S means event Start, E means event end. F means first date chosen, L means last date chosen. So for any given event, you have these possibilities:<BR><BR>1) --S---E---F---L---<BR>2) --S-----F---E---L---<BR>3) --S---F---L---E---<BR>4) ----F--S---E---L----<BR>5) ---F---S---L---E---<BR>6) ---F---L---S---E---<BR><BR>And of those possibilites, the ONLY ones where there is NOT any overlap are (1) and (6), right?<BR><BR>And the condition for (1) is<BR> E &#060; F<BR>while the condition for (6) is<BR> S &#062; L<BR><BR>SO:<BR><BR> WHERE eventEnd &#060; firstSelectedDate <BR> OR eventStart &#062; lastSelectedDate<BR><BR>Unless you allow your users to select time-of-day for the first/last dates, I&#039;d suggest you ignore the time portion of your events. That&#039;s easy: Just use the DATEVALUE() function in Access, which strips off the time.<BR><BR>

  4. #4
    Join Date
    Dec 1969

    Default IYAMADORK...

    Sorry! I have the WHERE *backwards*! That would find all the events *NOT* in the date range.<BR><BR>So:<BR><BR>WHERE NOT( eventEnd &#060; firstSelectedDate OR eventStart &#062; lastSelectedDate )<BR><BR>or<BR><BR>WHERE eventEnd &#062;= firstSelectedDate <BR>AND eventStart &#060;= lastSelectedDate<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts