Results 1 to 3 of 3
  1. #1
    Join Date
    May 2017

    Unhappy Unanswered: Format dates within an IIf statement Access 2010 Query

    Hi new here so please be patient if I make any errors.

    I am working on a query that I need to change dates in the future +8 years and minus 10. It's how the company identifies orders that are not yet ready to schedule.

    This is my IIf statement which returns the minus 10 on the year, BUT I want to format all of the records like this "mm/dd/yyyy"

    IIf(Year([Schedule Date])>=Year(Date())+8,Year([Schedule Date])-10,Year([Schedule Date])) I can get it to return the false statement by replacing the Year([Sced Date],"mm/dd/yyyy") but I am struggling to return the true statements correctly.

    Thanks in advance for any help you can provide.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 34
    use Date math functions:
    DateAdd("yyyy",8,[Schedule Date])

    DateAdd("yyyy",-10,[Schedule Date])

  3. #3
    Join Date
    May 2017

    Formatting IIf Statements

    Thanks ranman,

    I did try this Expr1: IIf([Schedule Date]>2920,DateAdd("yyyy",-10,[Schedule Date]),Format([Schedule Date]))

    In this scenario the dates that are over 8 years comes back minus 10 years which is perfect, BUT the current dates then minus 10 as well returning 2017 as 2007.

    These dates I want to remain the same. siiigghhh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts