Query comparing dates

[FJB]

I need help in designing and creating a select or parameter query. The
table has a date field which will need to be is a comparison with
today's date. There is one other date that needs to be considered.
This is the date that is three years from the date in the table. (1)
Should this be a calculated date in the table? (2) If not, the
calculation can take place in the query, right? (3) If there is an
expiration date available, the query will need to compare today's
date with the expiration date and show any records whose expiration
date is three months in the future, using the month and year only.

I apologize if this is not explained clearly. I will be happy to
clarify any point.

Frank

 

[Steve Schapel]

Frank,

No, it is not a good idea to store the calculated date in the table.

If I understand you correctly (which is not guaranteed... for one thing
I think you confused 3 months and 3 years?), you will put something like
this in the Criteria of the date field in the query...

 

[FJB]

Today I want to be able to determine which certificates will expire in
3 months. Certificates are good for 3 years. Therefore, certificates
whose "last date" is 10/5/02 will expire on 10/5/05. The suggestion
above does allow me to see those certificates which expire begnning in
October.

I believe I need to calculate an expiration date in the query and then
use the "DateAdd" function to extract those who match the criteria.

Any clearer?

Thanks for your help

 

[Steve Schapel]

FJB,

Yep, that's clearer

Yes, it looks to me like the simplest approach is to make a calculated
field in the query, like this...
ExpirationDate: DateAdd("yyyy",3,[Last Date])
....and then put the >=DateAdd("m",3,Date())
in the criteria of this ExpirationDate column of the query.

Is that right?

 

[FJB]

Many thanks.

The expiration Date worked great but the second criteria needs
tweaking. When I add >=DateAdd("m",3,Date()) in the criteria for the
"Expire Date" it gives me those certificates which will expire
beginning three months from now, not just those that will expire during
the third month.

 

[Steve Schapel]

FJB,

Ok, clearer and clearer. By "during the third month", do you mean that
today you would want to see all where the Expiration Date is between 1
October and 31 October? Or between 9 October and 31 October? Or
between 9 October and 9 November? Or something else?

 

[FJB]

If I accessed the database today or anytime during July, I would like
to see those certificate which expire during October. During August, I
would like to see those that expire in November. We just need to work
with the month on both values, correct?

 

[Steve Schapel]

FJB,

Ok, thanks for the further explanation. Try this...

Instead of the ExpirationDate calculated field in the query, as I
previously suggested, replace it with...
ExpirationMonth: Format(DateAdd("yyyy",3,[Last Date]),"yyyymm")
.... and in the criteria of this column, put...
Format(DateAdd("m",3,Date()),"yyyymm")

 

[FJB]

Many, many thanks! I am sure you will see more questions and I thank
you, in advance, for your help.

Frank