Queries to reports

[Cowboys822]

So i am having issues with one if my reports. All other reports are good except for this report. All other reports i have queries set up to pull data to generate a report. Example i have an expiration date from the qual date. I want to forcast who is expired, who is coming expired the current month, who is coming expired next month, 2 months out, 3 months out etc from the same report... all other reports i have does the similar for other training expirations. They are good since they are all yearly expirations. However, the one report i am having issues with has an expiration date of 8 motnhs instead of 12. If trading has been conducted before the 8th month the expiration date now goes a year from the qual date. My expression is a little complex because of what i want it to do. So i have seperate queries set up for expiration, current month, next month like the other reports those all pull the right dates from the database to the reports... so for the expired i have

Expiration qry / Exp: DateDiff(“m”, 8 [Qual])
Current month qry / Exp: DateDiff(“m”,7 [Qual])


next motnh qry / Exp: DateDiff(“m”,6 [Qual])
criteria i have for all is / Not is Null

[aytee111]

You want DateAdd, not DateDiff.

[Cowboys822]

You want DateAdd, not DateDiff.

Yes I already did and the names come out but on the next month and current month etc. it shows the same people with different dates from the qual date...

[aytee111]

Is it possible to store the expiration date on the table?

[orange]

It might be helpful to you and readers if you gave us a 4-5 line description of "what" your database/application is about - in simple plain English.

[Cowboys822]

Duh..........

[aytee111]

So there are two types of expiration - proficiency, which expires on July 31st (7 months), and qualification which expires on December 31st (12 months). And the purpose of this is to create a report showing who will be expiring this month, next month,.....

For each person
- if their qual exp date is before today, then they have expired, ignore them
- calculate the prof exp date - qual date + 7 (EOM)
- if the prof exp date is before today and they have not done the prof test then they have expired, ignore them
- if the prof exp date is before today and they have done the test, then exp date is qual date + 12 (EOM)
- if the prof exp date is after today and they have already done the test, then exp date is qual date + 12 (EOM)
- if the prof exp date is after today and they have not done the test, then exp date = prof exp date

Is this what you are trying to get to?

[Cowboys822]

Thanks for the reply back... so your first part is correct... we track over 300 people so I can’t ignore them. These reports shows the supervisors who are coming due.

So the qualification date is the day they conduct weapon training. It expires the same month at the end of the next year. So if they fired today, they will expire 31 Dec 18. Now what is tricky about this if they have to conduct another training between five and seven months after the qual or they go expired until they take the training. We try to get them in between those dates so they can continue to arm up. Once that training is completed the original qual does not expire until 31 Dec 18.

So I have dates I input onto my form:

Qual date once I enter the qual date it gives me the training due date. In this case would be 31 Jul 18. Now if the training is conducted say 15 Jul. that training date now turns to 31 Dec 18 since he completed the training. All that works perfectly. It also reflects perfectly on the main report I have. However, I created A new report to forecast who is coming due during the current month, next month, 2 months out etc. this forecast to the supervisors so they can get them into training.

I have three other reports for different training like I just explained and they work fine. This one is giving me the issue due to the complexity of the expression. Hope this explains it a little better...