Results 1 to 15 of 15
  1. #1
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66

    Help with IIF Expression

    I am in the process of building a DB to show Employee Training Records using MS Access 2010.



    I have a query which amongst other things consists of a Date Completed field (date), Frequency field (days) and Due Refresher Date field (which is a calculation of Date Completed + Frequency and displays a date).

    What I am trying to do is create a field where if the Due Refresher Date falls within say December of this year then the field will display a 1.

    I have tried using this expression; IIf([Due Refresher]=Month(Now()+3,"1","0") but it just displays a 0 where the Due Refresher Date is 13/12/2019.

    I am obviously doing something wrong in the expression and would appreciate if someone could advise me where I am going wrong.

    TIA

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your +3 is adding 3 days to now - and now includes a time element. suggest use the dateadd function and date function

    [Due Refresher]=month(dateadd("m",3,Date))


    I presume your due refresher field is an integer and not a date

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by mick3911 View Post
    I have tried using this expression; IIf([Due Refresher]=Month(Now()+3,"1","0") but it just displays a 0 where the Due Refresher Date is 13/12/2019.

    I am obviously doing something wrong in the expression and would appreciate if someone could advise me where I am going wrong.

    TIA
    Your expression has syntax and logical errors.
    Month(Now()+3,"1","0")
    Month(Now()+3) = 9
    Month(Now())+3 = 12
    #13/12/2019#<>12, allways.

    This is a correct expression:
    Code:
    IIf(month([Due Refresher])=Month(now())+3,"1","0")

  4. #4
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi Ajax,

    No, the Due Refresher field is a date.

    So if this date falls within December the new field will display a 1

  5. #5
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi accesstos,

    That works a treat, many thanks.

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    My pleasure!

    John

  7. #7
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi John,

    I lied, it does work if the refresher date is due in 3 months (Dec) time but it also displays a 1 if refresher is due in Dec 2020, 2021 etc.

    I guess that the year has to be included somewhere.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    John's suggestion will only work while the month of now is less than October.

    if october then Month(now())+3 will give you 13

    now you have now clarified that due refresher is a date, this will work

    Code:
    month([Due Refresher])=month(dateadd("m",3,Date))
    however that still does not take into account the year. i.e. if due refresher is 1/1/2018 or 1/1/2019 or 1/1/2020 they will all return true regardless of the year . I presume that does not matter

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    see you have found the problem

    try

    format([Due Refresher],"yymm")=format(dateadd("m",3,Date),"yymm")

  10. #10
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi Ajax,

    Still can't get this work. Is this full expression correct? Refresher Due In 3 Months: IIf([Due Refresher]"yymm")=Format(DateAdd("m",3,[Date]),("yymm"),"1","0")

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I'm sorry guys! It escaped from me that we don't have 13th month.

    I think that the safer is:
    Code:
    iif(DateSerial(year([Due  Refresher]),Month([Due  Refresher]),1) = DateSerial(year(date()),Month(date()+3),1),"1","0")
    Edit:
    Code:
    iif(DateSerial(year([Due  Refresher]),Month([Due  Refresher]),1) = DateSerial(year(date()),Month(date())+3,1),"1","0")
    Last edited by accesstos; 09-02-2019 at 05:01 AM. Reason: Code correction

  12. #12
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Gents,

    Appreciate your help with this.

    Still can't get this to work. I have attached a screenshot of the query if that helps.


    Click image for larger version. 

Name:	New Picture (16).jpg 
Views:	11 
Size:	79.3 KB 
ID:	39647

  13. #13
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Mike - I see a Month(date()+1) in the expression of the field. It must to be Month(date())+3

    It was my fault. I have edit the code of post #11.

    P.S.: Something is going wrong with me today...

  14. #14
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Yessssssssssssssss. Sorted. Cheers Aceesstos.

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551

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

Similar Threads

  1. Replies: 2
    Last Post: 03-11-2019, 01:26 PM
  2. Replies: 2
    Last Post: 11-16-2017, 03:33 AM
  3. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Replies: 4
    Last Post: 10-26-2012, 12:49 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