Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time span query (Access 2000)

    Now I know I've done this before, but for the life of me I can't remember how I did it;

    I've got a table with a [course name], [student],[startdate],[CompleteDate]. What I'd like to do is select those students who are either overdue for their course (3 months long) AND those who did finish it late.

    TIA
    Paul

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time span query (Access 2000)

    You could subtract the StartDate from the CompleteDate and do some arithmetic with that. I don't understand what you mean by "either overdue for their course (3 months long) AND those who did finish it late", in particular the AND.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time span query (Access 2000)

    I'll try to explain better (words are so inexact!)

    3 people do the same course ('a', 'b' & 'c')
    'a' completes his in four months
    'b' has not yet completed his
    'c' completed on time

    The query should select both students 'a' and 'b' (will need to show hsitorical data) but not student 'c'

    I'm struggling with selecting both of the scenarios shown by students 'a' AND 'b'

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time span query (Access 2000)

    In logical terms, you're looking for an OR condition, not an AND condition, that's why I was confused. "P And Q" means that a record has to satisfy both condition P and condition Q, at the same time. "P or Q" means that a record has to satisfy condition P, or condition Q, or possibly (but not necessarily) both P and Q.

    Your condition for CompleteDate would be

    Is Null Or > DateAdd("m", 3, [StartDate])

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time span query (Access 2000)

    Ahh, I was trying AND. Only trouble is, the students get recorded into the DB when they start, therefore they also get selected

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time span query (Access 2000)

    OK I've got it, not quite right (not sure on how to use months in the criteria), but I used the StartDate with this criteria;
    Not Between Now() And Date()-90

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time span query (Access 2000)

    Try

    Not Between DateAdd("m", -3, Date()) And Date()

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time span query (Access 2000)

    That works a treat. Thank you

Posting Permissions

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