Results 1 to 10 of 10
  1. #1
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273

    Expiration Date criteria


    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.

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    In your criteria for the date field, type the following:

    <Date() + 30

    Alan

  3. #3
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    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.

  4. #4
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    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

  5. #5
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    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

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664


    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....

  7. #7
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    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.

  8. #8
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    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

  9. #9
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    IssueDate and Expiration Date are both Date/Time format.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date between Criteria
    By sai_rlaf in forum Queries
    Replies: 2
    Last Post: 07-19-2011, 02:35 PM
  2. Queries Criteria with date
    By dssrun in forum Queries
    Replies: 4
    Last Post: 04-21-2011, 12:50 PM
  3. >Date() criteria
    By BED in forum Queries
    Replies: 3
    Last Post: 12-15-2010, 03:29 AM
  4. Criteria for date due
    By Desstro in forum Queries
    Replies: 4
    Last Post: 11-30-2010, 12:54 PM
  5. Date Criteria
    By tcollins02 in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 08:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums