Expiration Date criteria

[NISMOJim]

I have tried a few different expressions I've found on this site, but nothing is working.
I'm working on a query with an expiration date field [ExpDt]. I need it to display all records that are a month away from expiration, or have been expired for any amount of time so renewal notifications can be sent out. So far I'm still getting expiration dates from 2008 through 2014.
What expression should I enter in the criteria area?
Thanks for any & all help.

[alansidman]

In your criteria for the date field, type the following:

<Date() + 30

Alan

[NISMOJim]

That's one of the ones I tried, but I still get 143 out of 143 records with a date range from 2009 to 2014.

[alansidman]

okay. my thoughts for the next approach would be to try using the dateadd function.

Try this: <DateAdd("m",1,Date())

and look here for an explanation of DateAdd

http://www.techonthenet.com/access/f...te/dateadd.php

Alan

[NISMOJim]

Still the same result. On my input form the user enters the current date. At that time, the expiration date is calculated in the coding as the first day of the next month plus 3 years. That date pops up on the form automatically and is saved in the table. Could that be why the criteria isn't working? Might I need to instead work with the issue date (which equals Now() on the form in most cases) and add 2 years and 11 months to see the upcoming expirations?
Thanks for your quick response

[ssanfu]

First you say:

....display all records that are a month away from expiration, or have been expired for any amount of time....

Like alansidman said: <DateAdd("m",1,Date())

Then you say:

.....issue date (which equals Now() on the form in most cases) and add 2 years and 11 months to see the upcoming expirations......

I am so confused....

[NISMOJim]

In most cases, the issue date will be the current date that the form is filled out. A permit is good for 3 years. For example, if it is issued on the 19th of November 2009, it is good until the first of the next month, plus 3 years (1 December 2012). I'm trying to put together a query that our administrator can pull up to see what permits expire in the next month so notices can be sent to the users to renew before they expire. Included in this should be permits that have already expired, so a notice can be sent to them letting them know they are expired in case the user did not respond. I figured it would be easiest to start with the expiration date from the table and see which permits expire in the next month, or have already expired (<DateAdd("m",1,Date()) or <Date() + 30 ). Since that isn't working, I thought maybe it might be better to put the criteria on the [IssueDate] field that looks for permits that are coming close to expiration or already expired (2 years & 11 months from issue date or more). For some reason, the above expression in the [ExpDate] criteria still returns the original 143 records.
I hope that clears it up a bit.

[alansidman]

What is the format for the date field in your table? Is it set to a date format or is it text format and looks like a date format?

Alan

[NISMOJim]

IssueDate and Expiration Date are both Date/Time format.

[rpeare]

Can you post a sample of your database? (save it as 2003 if you can) so we can take a look, this should be an easy fix and I'm not sure why any of the suggestions alan's given you aren't working.