Results 1 to 11 of 11
  1. #1
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53

    Future date parameter


    I have a query for expiry dates..What I need to do is create a parameter to choose how many days ahead of todays date when I run the query each time (ie. 7 days,15 days,30 days etc.)I want to run this daily to find expiry date red zones

    Cheers!!!

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    What I understand is your requirement from your post is:
    1) Expirty Date is to be caculated from a particular Date.
    2) You want to specify the interval in Days.

    I have a query where I have a calculative field Expiry Date:

    The expiry Date is to be calulated on A_Date + No of Days you will specify.

    When I run this query, I will get a prompt Enter No Of Days as Access doesnpt find this Field in the query. I type the No.of.Days (integer) and the Expiry Date is calculated in the Field Expiry Date Accordingly.


    ExpiryDate: DateAdd("d",[Enter No Of Days],[A_Date])

    if this solves you problem mark the thread solved.

  3. #3
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Thank You for your reply...I need the expiry info from the date I run the query (ie.Today + 30 days) is there a way to have the date static?

    Cheers!!!

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I think you already have a field Expiry Date in the table.
    You want to look all the Data with the expiry date after a specific interval of time that will be specified in days from current date.

    Make aquery in the criteria of Expiry Date put the following expression:

    Between Date() And DateAdd("d",[Type No Of days],Date())

    This will prompt you to type the No.of Days and will display all results whic has expiry date between current Date and Current date + No.of Days Specified.

  5. #5
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Thanks for the reply while this works perfectly for one query...the data for the second query is from a linked table to AS 400 and cannot be changed...the date type from that table is as follows...20120319
    is there a way to change the format of the parameter?

    Thanks again
    Cheers!!!

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Use the table to create a query in access and in the query add a calculative field as: ExpiryDate: DateSerial(Left([bdate],4),Mid([bdate],5,2),Right([bdate],2))

    This will convert your date into the Short Date Format.

    Now you can use the previous expression as the criteria for this field.

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Mark this thread solved.

  8. #8
    normie is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    53
    Thanks for your help...It was what I needed...I can not see how to mark solved please let me know...Thanks again

  9. #9
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Thread Tools at the right hand side conner has the option. If you don't get it

    follow this link and learn:

    https://www.accessforums.net/forum-s...lved-1828.html

  10. #10
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    mark the thread solved.

  11. #11
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Please mark this thread solved

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

Similar Threads

  1. Replies: 1
    Last Post: 07-07-2010, 04:22 PM
  2. Date Parameter Help - prior 13 weeks
    By acw1980 in forum Access
    Replies: 1
    Last Post: 11-12-2009, 10:30 AM
  3. Question about the future of Access
    By kantell in forum Access
    Replies: 0
    Last Post: 11-04-2008, 11:43 AM
  4. Date Parameter
    By shakira in forum Queries
    Replies: 2
    Last Post: 03-09-2006, 12:55 PM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

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