Results 1 to 5 of 5
  1. #1
    Blayzze is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    2

    Exclamation Using Nested Iif with DateAdd in a query HELP!

    It seems to me this expression has worked before, but for the life of me I can't figure out what is wrong with it.



    "DueDate: IIf([CheckDate]<#3/31/2018#, DateAdd("m", 18, [CheckDate]), IIf([CheckDate]>#3/31/2018#, DateAdd("m", 24, [CheckDate]), Null))"


    I want to add 18 months to any date in the CheckDate field that is before 3/31/2018 and 24 months (2 years) to any date that is after 3/31/2018 and I want to do it in a query based on a table. I don't get any errors it adds the 18 months fine but it adds it to all dates not the specified criteria. I've tried changing it to "yyyy", 24 to no avail.

    Any help anyone can provide would be awesome!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Well, you have 3/31/2018 in both criteria. So that date does not get captured for either condition. You want to return Null if exactly 3/31/2018?

    Otherwise, the expression looks fine.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Blayzze is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    2
    I know the date issue, I was typing on the fly. Unfortunately I can't upload he database. Would it make any difference if the back end was on SQL Server as to why that expression isn't working correctly?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maybe. I don't use SQLServer but I have read that date/time fields are handled differently than Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What SQL Server driver are you using? Try to update it and see if makes a difference.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 16
    Last Post: 01-18-2016, 03:58 AM
  2. Replies: 7
    Last Post: 07-10-2015, 02:46 AM
  3. Replies: 3
    Last Post: 06-26-2015, 09:40 AM
  4. problem with union query with dateadd
    By manwe_sulimo in forum Queries
    Replies: 2
    Last Post: 01-25-2015, 12:35 PM
  5. Nested And/Or query
    By kagoodwin13 in forum Queries
    Replies: 2
    Last Post: 05-10-2012, 12:10 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