Results 1 to 7 of 7

how to combine iff and dateadd function

  1. #1
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    3

    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,148
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,413
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  4. #4
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    3
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,148
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  6. #6
    dave1962 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    3
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,148
    And what about the suggestion to eliminate the calculation altogether by setting option button values to 6, 12, 24?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

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, 09:48 AM
  2. used function DateAdd
    By azhar2006 in forum Queries
    Replies: 2
    Last Post: 10-07-2015, 02:31 PM
  3. DateAdd Function?
    By zburns in forum Reports
    Replies: 7
    Last Post: 06-23-2015, 12:55 PM
  4. DateAdd Function
    By armyofdux in forum Queries
    Replies: 12
    Last Post: 03-09-2015, 08:52 AM
  5. DateAdd Function
    By jschlapi in forum Forms
    Replies: 4
    Last Post: 10-23-2013, 12: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
  •  
Tech Forums: Microsoft Office Forums