# Thread: Time span query (Access 2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Time span query (Access 2000)

Try

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

8. ## 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
•