Results 1 to 4 of 4
  1. #1
    graemespence1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2

    expiry date query help

    Hello i'm new to the forum and in need of some help
    I have a table called members and I have a date field called "Date Joined"
    What I am wanting to do is have a query that will allow me to check if any members have expired as the memberships only last a year i dont have a expiry field is there any way to autogenerate a expiry date from the Date Joined field in either the query or in the table as a field so if i was to enter 01/01/2011 i would need the expiry date to be generated to show a year later at 01/01/2012. I'm ok to have this on a table field or on the fly on a query as long as I can auto generate it.
    I thank the forum in advance for any help they can give me and if you need more information just give me a shout.


    Graeme Spence

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Graeme;
    In a new column (field) in your QBE, type the following

    Expired: iif(Date()>DateAdd("yyyy",1,[Date Joined]),"Expired","Current")

    Now in the criteria for this same field type the following

    = Expired

    Run the query. You should get the results of all expirations.

    Alan

  3. #3
    graemespence1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    2
    Thanks that worked is there any way i can show the xpired date so if my start date was
    01/01/2010 so the generated expiry date would be 01/01/2011 is there i way i can see that date oir not no worry if i cant

  4. #4
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    In a new column type: ExpiryDate: DateAdd("yyyy",1,[Date Joined])

    Look here for information on DateAdd Function

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

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

Similar Threads

  1. Multiple Qualificaions with expiry
    By cottoman in forum Access
    Replies: 4
    Last Post: 10-25-2011, 06:15 AM
  2. Calculating Expiry Date -
    By Jojojo in forum Programming
    Replies: 12
    Last Post: 10-05-2011, 12:05 PM
  3. Query by date
    By fpmsi in forum Queries
    Replies: 4
    Last Post: 09-22-2011, 01:00 PM
  4. query to update a date based on another date
    By tkendell in forum Access
    Replies: 3
    Last Post: 06-29-2011, 09:32 PM
  5. Replies: 2
    Last Post: 07-31-2009, 06:56 AM

Tags for this Thread

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