Results 1 to 7 of 7
  1. #1
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5

    how to combine iff and dateadd function

    Hi there
    i'm trying to create a calibration database for work. I have a field which show the date of last calibration, called "date of last calibration". for the freq of calibration i am using a field with the option of 1,2,3 which equals 6 , 12 or 24 month. I am trying to add a field in a query to show the date when the next calibration is due. some thing like Datedue:iff([freq]=1,dateadd("m",6,[date of last calibration],iif([freq]=2,dateadd("m",12,[date of last calibration],iif[freq]=3,dateadd("m",24",[date of last calibration])))



    I just can't get it to work. Can any one help and show the correct formula i need to put in.

  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,819
    Consider:

    DateDue: DateAdd("m", Choose([freq], 6, 12, 24), [date of last calibration])

    Why don't you just make the choices of the field 6, 12, 24 ?

    Choose() will fail if [freq] is Null. Is that ever a possibility?

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in names.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by June7 View Post
    Choose() will fail if [freq] is Null. Is that ever a possibility?
    Either give freq a default value in the table field or the form control itself, or wrap freq in Nz function. Or your code could validate that freq isn't Null first.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Thanks for the reply. I got it sorted. Looks like i was getting mixed up with how many brackets to put in. I used the numbers 1,2,3 for the intervals as i wanted to used option buttons. It's years since i've used access and thats the way i remember using it. In the end i used the formula Cal Due: IIf([frequ]=1,DateAdd("m",6,[date of last calibration]),IIf([frequ]=2,DateAdd("m",12,[date of last calibration]),IIf([frequ]=3,DateAdd("m",24,[date of last calibration]))))
    Again thanks for the reply

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Why the loooong, hard-to-read expression instead of the short one?

    Option buttons can have values 6, 12, 24. Which would eliminate need for translation calc.
    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.

  6. #6
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    Thanks June I didn't realise I could use something as simple as"choose". I change it to that

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    And what about the suggestion to eliminate the calculation altogether by setting option button values to 6, 12, 24?
    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.

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

Similar Threads

  1. Dateadd function
    By JackieFeng in forum Access
    Replies: 2
    Last Post: 05-02-2016, 10:48 AM
  2. used function DateAdd
    By azhar2006 in forum Queries
    Replies: 2
    Last Post: 10-07-2015, 03:31 PM
  3. DateAdd Function?
    By zburns in forum Reports
    Replies: 7
    Last Post: 06-23-2015, 01:55 PM
  4. DateAdd Function
    By armyofdux in forum Queries
    Replies: 12
    Last Post: 03-09-2015, 09:52 AM
  5. DateAdd Function
    By jschlapi in forum Forms
    Replies: 4
    Last Post: 10-23-2013, 01:36 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