Date Ranges

[sTrongFuse]

OK, Very quick one. I have a report that lists the expiry dates of certain items. Control name = [ExpiryDate]. At present, my report works fine but lists the expiry date of every item in the database.

What I want it to do is only return entries where the expiry date is 12 weeks or less from today.

If someone could tell me the SQL code to insert into my query to do this, I'd be extremely grateful.

Cheers,

T

 

[Mr. B]

The DateAdd function should provide what you need.

In the criteria of the "ExpiryDate" field in your query try using:

<=DateAdd("w",12,Date)

This should return only records where the "ExpiryDate" is less than or equal to the date that is 12 weeks from the current date.

 

[sTrongFuse]

The DateAdd function should provide what you need.

In the criteria of the "ExpiryDate" field in your query try using:

<=DateAdd("w",12,Date)

This should return only records where the "ExpiryDate" is less than or equal to the date that is 12 weeks from the current date.

Thanks. Was giving me a type mismatch error but changed it to <=DateAdd("w",12,Now)and it worked fine.

Cheers,

T

 

[Mr. B]

You evidently have your field set to a "general" date type format. This will mean that all dates will be evaluated not just by date but even down to the second. I was assuming that you only needed to evaluate by the date only.

Glad you got it to work.

 

[sTrongFuse]

You evidently have your field set to a "general" date type format. This will mean that all dates will be evaluated not just by date but even down to the second. I was assuming that you only needed to evaluate by the date only.

Glad you got it to work.

I'll take your word for that. I have to enter my dates in US format then have access convert them back to the "proper" UK format. I work for a US company and my PC has to be set up as US regional so I can access some of their systems even though I'm in the UK. It causes some strange results sometimes... :banghead: