Weary Of DateAdd Query

[Alex2015]

My database consists of tables with employee info, where they work, training modules, & the respective junction tables.

For the training modules, there are employee completion dates for all of them. Naturally, I wrote a query that calculates the due dates for each module as follows (for something that's due every 12 months anyway):

DueDate: DateAdd("yyyy",1,[CompletionDate])

So far, so good. I next wanted to give my users the ability to return values for things like, "I want to see who is going to be due for something this month, or who is going to be due for something in the next six months, etc., or basically whatever date range for due dates they can think of." I thought including the following expression in the criteria line for the DateAdd function would solve this:

Between [Enter the start date (m/d/yy):] And [Enter the end date (m/d/yy):]

This will the prompt the user to enter dates but the results won't match or are totally out of range for what they input. I even tried to write a query from within a query that calculated the DueDate first & then asked the questions for the date range. Does anyone have a non-VBA solution for this? Thank you & happy holidays.

 

[arnelgp]

what i was thinking is to have a separate query for each due date you wish to process.
1. due this month
select * from your table where DateDiff("m", [CompletionDate], Date) = 0

2. due next month
select * from your table where DateDiff("m", [CompletionDate], Date) = 1

3. due on six month from now
select * from your table where DateDiff("m", [CompletionDate], Date) = 6

etc...

 

[JHB]

I thought including the following expression in the criteria line for the DateAdd function would solve this:

Between [Enter the start date (m/d/yy):] And [Enter the end date (m/d/yy):]

It should be in the criteria for the [CompletionDate].

 

[Alex2015]

It should be in the criteria for the [CompletionDate].

Yes; I've had the "Between" code in the criteria line for the completion dates before but it doesn't return the correct results. For example, if an employee completed training module A on 11/26/14 & I wanted to see who would be due for November 2015, the "Between" code wouldn't show me module A as being due for this person.

However, if I wanted to see who completed module A for November 2014 & typed "11/1/14 & 11/26/14" for the start & end dates, then it would show me this particular employee. I don't want to see who completed what during month(X), I want to see who is due for what for a specified date range. Placing the "Between" code in the criteria line for DueDate doesn't solve this problem. There's got to be something I'm missing.

 

[Alex2015]

what i was thinking is to have a separate query for each due date you wish to process.
1. due this month
select * from your table where DateDiff("m", [CompletionDate], Date) = 0

2. due next month
select * from your table where DateDiff("m", [CompletionDate], Date) = 1

3. due on six month from now
select * from your table where DateDiff("m", [CompletionDate], Date) = 6

etc...

I'm going to give this a try right now. Is there any way to prompt the user for a date range instead of having to write so many queries though?

 

[arnelgp]

ok, so you want to create a query that will return [CompletionDate] that is between two dates:

select * from yourTablename WHERE DateAdd("yyyy",1,[CompletionDate]) Between [Enter start date (m/d/yy):] And [Enter end date (m/d/yy):]

or is it the same with your query?

 

[Alex2015]

ok, so you want to create a query that will return [CompletionDate] that is between two dates:

select * from yourTablename WHERE DateAdd("yyyy",1,[CompletionDate]) Between [Enter start date (m/d/yy):] And [Enter end date (m/d/yy):]

or is it the same with your query?

Oh, sorry man. You must have read me wrong in the second paragraph above. A date range for CompletedDate is not something I want but know how to do. What I really want is a date range for DueDate instead. Kinda tough to figure this out.

 

[arnelgp]

it is Duedate actually (from your post adding 1 year from CompletionDate).