Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Show contents where date is greater than yesterday plus 10

    I am trying to retrieve the event contents of an Access database, using ASP, by the date field where the date is today <= 10 days in advance. I have a field for the event date = DATE, from table FIXTURES, event = Event_id.

    I normally use the following to produce my ASP pages with no trouble;

    <% sqlString = "SELECT ......................................"
    SET RS = oConn.Execute(sqlString)
    WHILE NOT RS.EOF
    %>

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    SELECT * FROM FIXTURES WHERE Event_id = " & event & " AND DATE <= DATEADD(d, 10, GETDATE())"

    Or if the date you want to add 10 days to is a variable in ASP:

    SELECT * FROM FIXTURES WHERE Event_id = " & event & " AND DATE <= DATEADD(d, 10, '" & date & "')"
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I should make a few notes.. in the title of your post you say "where date is greater than yesterday plus 10"

    today >= (yesterday + 10)

    But the text of the post reads "where the date is today <= 10 days in advance".

    today <= (today + 10 days)

    I gave you an example based upon the later... If you need an example of the former, it would be: DATE >= DATEADD(d, 9, GETDATE())

    I should note that I didn't put a lower limit on that... so a more complete example might be:

    SELECT * FROM FIXTURES WHERE Event_id = " & event & " AND DATE >= GETDATE() AND DATE <= DATEADD(d, 10, GETDATE())"

    This would give you all rows who's "date" field is greater than, or equal to, today, but less than, or equal to, 10 days in advance. To go backwards in the calendar (to get say, yesterday's date), you would use a negative number: DATEADD(d, -1, GETDATE())
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Apr 2004
    Posts
    2
    I have tried your statements but just get the one error;

    Microsoft JET Database Engine error '80040e14'

    Undefined function 'GETDATE' in expression.

    I am missing something completey or should i really just give up and go back to my PS2

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    That would be due to Access. I assumed this was MS SQL Server. Substitute Now or Now() (I can't remember which) for GETDATE().
    That which does not kill me postpones the inevitable.

Posting Permissions

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