Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    neil12sc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5

    Using if or else in a form field to to Calculate number of years

    Dear Sir / Madam

    I have a problem in calculating number of years pertaining to retirement of a teacher. The problem is as follows

    ID
    Name
    Registered Date - Reg
    Left - Left
    Rejoined - Returned
    Left again - Left2
    Rejoined Again - Returned2
    Retired - Retired

    Service to date - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],Date())/365.25)

    Full years of retirement - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],[Retired])/365.25)

    Above codes are stipulated in from fields

    Can you help me to get following answered.

    How to use if or else to calculate

    1. For Example if the teachers retire prior to the todays date how to get Total service years.



    2. Full years of retirement

    I think I get the correct answer for the 2nd. But if the teacher retire before the todays date I do not get the correct answer.

    Please help.

    Thank you.

    Wilfred.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is your formula:
    'what you show
    [#yrs]+[#yrs]+ ([#yrs] / 365.25)

    or
    ([#yrs]+[#yrs]+ ([#yrs]) / 365.25

  3. #3
    neil12sc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    Thanks for the prompt reply. I may have to explain the problem. It is program I made for teachers of Sunday School.

    Teachers get registered to the Sunday School with an application form. Teacher has to fill the registered date in the form. That date I have named as Reg, in the form field. Teachers are allowed to leave the school for taking of examinations, wedding etc. Left and Left2 are for leaving dates. Then when they come back as they are rejoining again I have given name as for the field as Returned. The period between Reg and Left is first served period in the school and when they leave for the second time and the period between returned and Left2 is a serving period etc. Then period between Returned2 and Retired is the final serving period. Some teachers never take leave. Some teachers leave for the fist time and never returns.

    We have to add all these periods and get the total years served.

    What we want to find is Serving period in years at a given date which we take as Date() and calculate the served period. If the teachers retire prior to the todays date, how to get Total service years because by then teacher has got retired.

    Hope I am not troubling you.


    Thank you.

    Wilfred




  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think that you db is normalized correctly. What if a teacher has more that two periods of leave?
    BTW "Name" is a reserved word and should not be used as the name/title of any object (field, form, report etc)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I think Bob meant NOT normalised correctly.
    Apart from the points already made there are several issues here. For example
    1. If someone starts in say Jan 2018 and leaves in Dec 2018, rejoins in Feb 2019 and leaves again in Dec 2019, your expression would count those two combined portions as zero years when it should be 21 months (1 year 9 months)
    2. A year isn't exactly 365.25 days. Whilst it probably won't affect years of service, it could conceivably cause discrepancies.

    I recommend you treat each section use a modified age function which allows for part years correctly, then add them together before rounding.
    There are numerous age function examples available online including several in this thread at another forum https://bytes.com/topic/access/answe...invalid-syntax
    The one is usually use is by far the simplest
    Code:
    DateDiff("yyyy", [DOB], Date) + (Format([DOB], "mmdd") > Format(Date, "mmdd"))
    Last edited by isladogs; 01-14-2021 at 01:12 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by isladogs View Post
    I think Bob meant NOT normalised correctly.
    Yes, indeed. Thank you Colin. As alert and eagle eyed as ever, I'm glad to see
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    neil12sc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    Dear Mr. Collin,

    Thanks for the reply. How pl let me know for for it. The above code not the correct answer to my problem. That is, if the teachers retire prior to the todays date, how to get Total service years because by then teacher has got retired. Also how to use If and then in this instant

    Thank you.

    Wilfred.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You need to replace the DOB & Date fields with those in your database. Possibly this:

    Service to date = DateDiff("yyyy", [Reg],[Left]) + (Format([Reg], "mmdd") > Format([Left], "mmdd")) _
    + DateDiff("yyyy", [Returned], [Left2]) + (Format([Returned], "mmdd") > Format([Left2], "mmdd")) _
    + DateDiff("yyyy", [Returned2], Date()) + (Format([Returned2], "mmdd") > Format(Date(), "mmdd"))

    NOTE: I've just used the field names from your original incorrect expression.
    You used Date() in your expression so I have as well. Perhaps that should be RetirementDate instead.

    The above doesn't need Int as each part of the expression is an integer

    However, what if someone leaves and returns 3 or more times before retiring. Your field structure can't handle that
    That's why it needs to be normalised.

    Also you may get errors if someone has no [Left] data (another bad choice of field name) or Returned, Left2, Returned2 data
    So you may need to tweak it further
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To answer your particular question using your existing formula please try this:
    -Add these two functions to a standard module
    Code:
    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
      Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
      Max2 = IIf(a > b, a, b)
    End Function
    Modify your formula:
    Service to date - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],Min2(Date(),[Retired]))/365.25)

    Again, I would follow the suggestions offered here, but I think this would address your current question.

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

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry Vlad but your suggestion only covers part of the problem.
    It won't solve specific examples like the dates I gave in point 1 of my first reply - see post #5
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I know Colin, that is why I put the qualifier:
    To answer your particular question using your existing formula please try this:
    , I wanted to show the OP how to deal with his specific question of how to easy get the minimum of two dates (retirement vs Date()).

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

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK thanks - that went over my head
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    neil12sc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    Dear Vlad Cucinschi,

    Thank you for assistance. Now the new code added above that is
    Min2(Date(),[Retired])), I have a question in my mind.
    I am not making any query but inserting codes to a form field.

    Where am I to insert these conditions as above?

    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
    Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
    Max2 = IIf(a > b, a, b)
    End Function


    Thank you.
    Wilfred.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Wilfred,

    You need to add this (and maybe the Max2) function to a standard VBA module. In the Create ribbon click the Module button and paste the two functions.

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

  15. #15
    neil12sc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5

    Cool

    Dear Mr. Collin,

    I thank you for assisting me to get the answer for my specified question.

    This the code you gave me.

    Service to date = DateDiff("yyyy", [Reg],[Left]) + (Format([Reg], "mmdd") > Format([Left], "mmdd")) _
    + DateDiff("yyyy", [Returned], [Left2]) + (Format([Returned], "mmdd") > Format([Left2], "mmdd")) _
    + DateDiff("yyyy", [Returned2], Date()) + (Format([Returned2], "mmdd") > Format(Date(), "mmdd"))

    This problem was discussed in this forum before. Some of your friends also participated in the discussion. The above code gave the Service to date. Say today is 02/02/2021. and in the above code by using say the exact date he retired instead of Date() Service can be found.
    But if the teacher did not take leave and went on retirement before the retired date the full code cannot be used to find the service. Is there a method of using SAY if or else any other conditional in the form field SERVICE to get the Total Service by using the same code..

    What happens is when fields like the left, left2 returned and Returned are blank the above cod will not work.

    Please help.

    Thank you.

    Wilfred
    Last edited by neil12sc; 02-02-2021 at 08:57 AM. Reason: Grammer mistake

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2019, 02:35 PM
  2. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  3. Replies: 3
    Last Post: 09-22-2014, 04:38 PM
  4. Calculate number of years.
    By sha1023012 in forum Access
    Replies: 4
    Last Post: 10-02-2012, 08:33 AM
  5. Replies: 1
    Last Post: 11-12-2010, 01:16 AM

Tags for this Thread

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