Calculating a future date from an existing date in a query

K

KlausAlt

I tried once before, but didn't ask the right question. Here is what I
have. I have a query and one of the fields that is in the query is a
date field. The date that is expressed is the date that a certain
document was issued on. The documents expire in one year, so I want to
run a query that will give me the expired documents. I tried several
different ways, and the only way so far I have been able to do this
using this command: Between #2/1/2004# And #4/1/2005# in the criteria
field. I have tried :>Date()+365, :Date()>365 with no success.

What I want the query to do is pull up all records older than one year
from date of issue. The above command gives me all the records that
expired from February 04 to April of 05, which works. However, I have
to change the criteria each month to get my report. Any ideas would be
much appreciated.

Thanks.
 
T

Tom Ellison

Dear Klaus:

It seems you were pretty close. Likely:

< Date() - 365

would have worked pretty well, except that, when there is a leap year, it is
366.

The most thorough approach would be to use the DateAdd() function and have
it back up 1 year. There is a good explanation of this function in the
online help.

Please note that the Date() function depends on the computer's system date
being set properly. If that date is wrong, then all sorts of bad things can
start happening. There are various ways of protecting yourself from that.

Tom Ellison
 
T

Tom Ellison

Dear Klaus:

I would have to say that depends on what you want the query to do.

Is it an action query? Do you just want to see a datasheet of the query's
results?

I do not typically EVER display a datasheet to users. I give them forms and
reports, snapshots, and exported spreadsheets.

But then, I also never use a switchboard.

I can try to help you with this if you like. Perhaps you would do well
starting a new thread and getting another opinion.

Tom Ellison
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top