Results 1 to 5 of 5
  1. #1
    QTip is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    3

    Date expression issues

    Good morning all!

    Not sure if it's because I had too long of a Christmas break or what, but I just can't seem to get my head around trying to write a date expression for the following:

    I need to filter a query to give me everything for the previous year but only until Feb 15 of the current year.

    Right now I have an expression that as soon as a new year starts everything in the previous years are excluded. What I really need is to still show all the previous years records until at least the 15th of February in the current year. I want this to change automatically ie: I don't want to hard code the dates into the query.



    Example:

    Todays date is Jan 6/2011 - I want to show all purchase orders for 2010 plus any that have been issued in 2011.

    Todays date is Feb 16/2011 - I want to show only the purchase orders issued in 2011.

    I hope that this makes sense.

    Thanks
    QTip

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    [issue year]=iif(format(date(),"mmdd")<="0215",year(date())-1,year(date()))

  3. #3
    QTip is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    3
    Hi Weekend00

    Thank you for taking the time to help me out.

    I don't know if I'm missing something or if I just didn't give enough information, but when I try your expression it shows everything.

    I probably should've said that I need to compare the current date against the purchase order [OrderDate] and if the [OrderDate] is between Jan 1 and Dec 31 of the previous year AND the current year's date is before Feb 15 than show those records ELSE only show the current year PO's.

    Please bear with me. Date expressions and me do not seem to get along LOL!

    Thank you for your guidance.
    QTip

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    did you changed [issue year] of
    [issue year]=iif(format(date(),"mmdd")<="0215",year(date())-1,year(date()))

    to year([orderDate]) ?

    the final condition is:
    where year([orderDate])=iif(format(date(),"mmdd")<="0215",year(date())-1,year(date()))

  5. #5
    QTip is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    3
    You are fabulous! Thank you so much. I've been working on this for 2 days & I just couldn't get my head wrapped around it. For whatever reason I had only put [Orderdate] instead of Year([OrderDate]) . Once I followed your final condition it worked beautifully.

    Once again, Thank you I really do appreciate your help.
    Qtip

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

Similar Threads

  1. Error in query expression Date()
    By boywonder in forum Queries
    Replies: 15
    Last Post: 12-26-2010, 06:04 PM
  2. Date of Birth Access expression
    By Father John in forum Access
    Replies: 5
    Last Post: 12-02-2010, 10:33 PM
  3. Message box issues.
    By thart21 in forum Programming
    Replies: 2
    Last Post: 05-29-2010, 12:58 AM
  4. Replies: 1
    Last Post: 03-23-2010, 04:01 PM
  5. Subform Issues
    By dromorkid in forum Forms
    Replies: 0
    Last Post: 11-24-2008, 11:35 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