Trying to query dates

[UT227]

I have a query where I've calculated the expiration dates to some classes. I'm trying to see only those classes that are coming up to expiration. I have a form with a text box. I want that text box to indicate how many months I want to search for an expiration. I want to see only those classes that are going to expire in [Variable] months. I'm having a hard time coming up with a way to do that.

[UT227]

This is what I've tried. I subtracted today's date from the date of Training: DaysPassed: [Date()]-[LastOfDOT]
Then I took how many days till it expires and subtracted how many days passed which should give me how many days till it expires. Then dividing by 30 should give me the months till it expires: Int([DaysToExpire]-[DaysPassed]/30)
I'm having a hard time after that though. On the query, I used the criteria: <= ([Forms]![frmSwitchboardTraining]![txtMonths]). I fill in the txtMonths with a number, and it doesn't calculate it right. However, if I put it directly into the query: <=4, it works perfectly.

[orange]

Usually when looking for a difference in dates , you would use dateDiff() function.
When you have a calculation [Int([DaysToExpire]-[DaysPassed]/30)] that deals with "months", you are in fact dealing with an approximation --since not all "months" have the same number of days.

I'm having a hard time after that though

Can you describe what you would like to do in plain, simple English --no jargon and no database terms.

[UT227]

Thanks. I forgot about the DateDiff. I still have the same search problem thought. I'm not writing my criteria correctly. When I try to query "2" months, it gives it correctly.



But when I add the search from a form, it will not give the correct query. In these, I'm still trying to query "2" but from a form.



I'm obviously not writing the criteria correctly. Ideas?

[Minty]

Copy the Criteria into a field in your query, see what it thinks it is.

[UT227]

I didn't understand. Copy the Criteria into a field in my query? The criteria is in the query.

[Minty]

Yes copy it and paste it to the top line of your query designer, and the value will appear as a query field on every line, just for debugging purposes.

[UT227]

I'm still not following. Top line of my query designer? I don't understand this. Where? Go to SQL? I tried to put it in the field, table, and sort. Of course, it rejected it. I have the criteria in the first line of the criteria field. I don't know of another place to write it.

[UT227]

Is there a way you can show a pic of what you're saying?

[Minty]

Yes - something like this ;

[UT227]

[Minty]

Okay so now run that with your form open and a value in the form? and show us the result?

[Micron]

In the MonthsLeft query field, where are you getting those values from? Can't see them in the rest of the query design, nor the table/query datasheet results.
When the query is run, the form is open in form view, with the textbox containing a value?
Edit: forgot to say that these two parts are contradictory

I subtracted today's date from the date of Training:

DaysPassed: [Date()]-[LastOfDOT]

Your calculation is the opposite of your statement. It could be that this is why it works for "2" when you say

I fill in the txtMonths with a number, and it doesn't calculate it right

That statement doesn't help much.

[UT227]

Using the form for its value:

[UT227]

I did a DateDiff to get that field: