Trying to query dates

[orange]

UT227,

Can you tell us what the first 5 lines in your post #14 mean in your set up?
You have expiryDates in the past, current year and future.



I'm not sure readers (me) is understanding your issue.

[UT227]

I have classes (certifications) that expire. They expire at different dates. In order to prepare for people expiring, I need to know when they expire. I want to look ahead X many months and see which classes (certifications) are about to expire. Example: I'm looking ahead 4 months to see which classes are going to need to be scheduled.

[orange]

I suggest you work with Days ---not Months for consistency.

You have
-a Person who may have 1:M Certifications
-a Certification has a specific StartDate and ExpiryDate


Person -->PersonHasCertification<--Certification

** seems you want to know which Person(s) have Certification(s) that will Expire at some future ExpiryDate

** are you trying to determine when specific classes/certifications need to be scheduled?
** does having a class depend on the number of Person(s) involved?

??Do you have some mechanism to identify Person(s) whose Certification(s) has/have expired??
??Do you care about expired certification??

Can you tell us how you interpret the first 5 rows as mentioned in my previous post?

Good luck.

[UT227]

Those are classes that expire. The [MonthsLeft] is how many months are left until that person needs to take the class again. I took the difference between today's date and the date of the last class. I then took the difference between the expiration date and that date. That gave me how many more months there were left until it expires.

[UT227]

I'd rather go by months for a search. I only need a general overview of everyone that will be expiring in whichever classes are about to expire. I'm not concerned with those already expired as they should be lumped together with those about to expire. That's why I want a search that is less than or equal to the number I'm looking for. If they are already expired, the number should be negative for them and that will be less than what I'm looking for. So, it should appear in my query. I do not need to look for a specific class. I already have a search for specific classes and persons. Classes are not dependent on the number of people in the class.

[orange]

So the -20 and -31 have relevance to you.
What does a negative [MonthsLeft] mean? They are all expired, right?

[UT227]

Yes, negatives are all expired

[Micron]

Still no idea where the [DaysPassed] and [DaysToExpir] are coming from. Maybe it's not relevant...

I took the difference between today's date and the date of the last class. I then took the difference between the expiration date and that date. That gave me how many more months there were left until it expires.

Wouldn't you get the same thing by subtracting Date from expiry date (in one calculation)? Future expiry would be positive, past expiry negative.

[orange]

I agree with Micron's view.

[UT227]

Absolutely. I appreciate the insight. That worked very well. I got the same results, but there were less calculations. I sill have the same problem though. When I search in the query <=2, it gives me the correct results. When I use <=[Forms]![frmTrainingSearch]![txtMonths], it gives me incorrect results. Any ideas?

[Micron]

I just noticed something. In post 11 pics, you got a parameter prompt from the query and had to enter 2 yourself. This probably means your control reference is bad. Either the form or control name is mis-spelled or it resides on a subform. Can't recall if you'd get the prompt if the form wasn't open in form view (thinking the query would just run) so I'll not suggest that. Besides, I brought this up in an earlier post of mine, so I'm going with bad reference. If that's not it, I suggest you post a zipped copy of your db. If there is sensitive data in it, remove all as necessary but leave in something that will allow the required queries and forms to work.

EDIT: or the form resides on a navigation form.

[UT227]

It's getting the text box off the form just fine. I just figured out that It's the < that is causing the problem. When I use = it works just perfectly. When I just use the < or > it gives the wrong return.

[UT227]

This is kinda interesting. When I use 1 or 10 or more, I get back a good return. 2-9 gives back a bad return, it shows all records.

[Minty]

Okay that is strange - can you post up a sample set of data and the query and just the form you are using.

[UT227]

I took out as much as I could. I attached a small database. It seems to be doing the same thing.