Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

calculating months or the end of year.

Discussion in 'Information Technology' started by JerryR, Jul 28, 2006.

  1. JerryR

    JerryR
    Expand Collapse
    Guest

    In my database I have a due date (entered by users). I need to calculate a
    compliance date. Which is 3 months from the due date OR the end of the
    calendar year whichever comes first. Is there an If or Else statement I can
    use ?
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010
    Malaysia Second Malaysian Plane Lost in only 4 months! Breaking News Jul 18, 2014
    UK In eight months the Gurdwara has already made a big impact on Glasgow Breaking News Jan 7, 2014
    USA Bin Laden’s last stand: In final months, terrorist leader worried about his legacy Breaking News May 1, 2012
    Project Conversion: 12 Months of Spiritual Promiscuity Interfaith Dialogues Aug 16, 2011

  3. Ron2006

    Ron2006
    Expand Collapse
    Guest

    In afterupdate event of duedate


    me.compliancedatename = dateadd("m",3,me.duedate)
    if not year(me.compliancedatename) = year(me.duedate) then
    me.compliancedatename = "12/31/" & year(me.duedate)
    endif



    JerryR wrote:
    > In my database I have a due date (entered by users). I need to calculate a
    > compliance date. Which is 3 months from the due date OR the end of the
    > calendar year whichever comes first. Is there an If or Else statement I can
    > use ?
     
  4. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    Hi Jerry - try this

    IIF(DateAdd("m",3,[due date]),#31/12/2006#,[due date],#31/12/2006#)
    the dateadd adds the 3 months on. You could also have a field where you
    store the end of the calendar year instead of typing it in direct. Hope this
    helps you -- Dika

    "JerryR" wrote:

    > In my database I have a due date (entered by users). I need to calculate a
    > compliance date. Which is 3 months from the due date OR the end of the
    > calendar year whichever comes first. Is there an If or Else statement I can
    > use ?
     
  5. BruceM

    BruceM
    Expand Collapse
    Guest

    As a variation of an earlier suggestion, this could be a calculated query
    field:
    ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])

    If you already have a field named ComplianceDate, just change the name of
    the calculated field.

    Alternatively, with an = sign instead of CompDateCalc: it could be the
    control source of an unbound text box. Since it is a calculation there is
    no apparent reason to store the number. Making it a calculated query field,
    and binding a text box to that field, would let you easily use the result in
    forms or reports by basing the form or report on the query and binding a
    text box to the query field.

    "JerryR" <JerryR@discussions.microsoft.com> wrote in message
    news:B8958290-6157-49EA-BB7A-133BBA587A89@microsoft.com...
    > In my database I have a due date (entered by users). I need to calculate
    > a
    > compliance date. Which is 3 months from the due date OR the end of the
    > calendar year whichever comes first. Is there an If or Else statement I
    > can
    > use ?
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

    I believe that all the references to "Date()" in the code should
    actually be [DueDate] to make it accurate no matter what timeframe the
    data is being viewed.

    > ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    > Year([DueDate]),DateSerial(Year([DueDate]),12,31),DateAdd("m",3,[DueDate])


    Using the other code, if you are in December and have a due date of
    January of the next year it would not give the proper compliance date.
    Or if the due date is November of last year it would also not give the
    proper compliance date - it would say Feb of this year instead of
    December 31 of last year.

    Ron

    BruceM wrote:
    > As a variation of an earlier suggestion, this could be a calculated query
    > field:
    > ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    > Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])
    >
     
  7. JerryR

    JerryR
    Expand Collapse
    Guest

    This is what I was looking for. One small problem though. With a due date of
    03-03-2007 the compliance date will display as 12-31-2006. Is there a work
    around for that /

    "BruceM" wrote:

    > As a variation of an earlier suggestion, this could be a calculated query
    > field:
    > ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    > Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])
    >
    > If you already have a field named ComplianceDate, just change the name of
    > the calculated field.
    >
    > Alternatively, with an = sign instead of CompDateCalc: it could be the
    > control source of an unbound text box. Since it is a calculation there is
    > no apparent reason to store the number. Making it a calculated query field,
    > and binding a text box to that field, would let you easily use the result in
    > forms or reports by basing the form or report on the query and binding a
    > text box to the query field.
    >
    > "JerryR" <JerryR@discussions.microsoft.com> wrote in message
    > news:B8958290-6157-49EA-BB7A-133BBA587A89@microsoft.com...
    > > In my database I have a due date (entered by users). I need to calculate
    > > a
    > > compliance date. Which is 3 months from the due date OR the end of the
    > > calendar year whichever comes first. Is there an If or Else statement I
    > > can
    > > use ?

    >
    >
    >
     
  8. JerryR

    JerryR
    Expand Collapse
    Guest

    Thanks this'll work.

    "Ron2006" wrote:

    > I believe that all the references to "Date()" in the code should
    > actually be [DueDate] to make it accurate no matter what timeframe the
    > data is being viewed.
    >
    > > ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    > > Year([DueDate]),DateSerial(Year([DueDate]),12,31),DateAdd("m",3,[DueDate])

    >
    > Using the other code, if you are in December and have a due date of
    > January of the next year it would not give the proper compliance date.
    > Or if the due date is November of last year it would also not give the
    > proper compliance date - it would say Feb of this year instead of
    > December 31 of last year.
    >
    > Ron
    >
    > BruceM wrote:
    > > As a variation of an earlier suggestion, this could be a calculated query
    > > field:
    > > ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    > > Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])
    > >

    >
    >
     
  9. BruceM

    BruceM
    Expand Collapse
    Guest

    I believe you are correct, even if the due date is set to October 1 of next
    year or later. I had for no good reason assumed a shorter time frame.

    "Ron2006" <ronnemec@hotmail.com> wrote in message
    news:1152292260.307147.100320@m73g2000cwd.googlegroups.com...
    >I believe that all the references to "Date()" in the code should
    > actually be [DueDate] to make it accurate no matter what timeframe the
    > data is being viewed.
    >
    >> ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    >> Year([DueDate]),DateSerial(Year([DueDate]),12,31),DateAdd("m",3,[DueDate])

    >
    > Using the other code, if you are in December and have a due date of
    > January of the next year it would not give the proper compliance date.
    > Or if the due date is November of last year it would also not give the
    > proper compliance date - it would say Feb of this year instead of
    > December 31 of last year.
    >
    > Ron
    >
    > BruceM wrote:
    >> As a variation of an earlier suggestion, this could be a calculated query
    >> field:
    >> ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    >> Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])
    >>

    >
     
  10. BruceM

    BruceM
    Expand Collapse
    Guest

    Now I see that the flaw with using Date() is more in the DateSerial part of
    the IIf statement, even with a short time frame, than in the comparison
    part.

    "BruceM" <bamoob@yawwhodawtcalm.not> wrote in message
    news:%23TAUXzeoGHA.3808@TK2MSFTNGP03.phx.gbl...
    >I believe you are correct, even if the due date is set to October 1 of next
    >year or later. I had for no good reason assumed a shorter time frame.
    >
    > "Ron2006" <ronnemec@hotmail.com> wrote in message
    > news:1152292260.307147.100320@m73g2000cwd.googlegroups.com...
    >>I believe that all the references to "Date()" in the code should
    >> actually be [DueDate] to make it accurate no matter what timeframe the
    >> data is being viewed.
    >>
    >>> ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    >>> Year([DueDate]),DateSerial(Year([DueDate]),12,31),DateAdd("m",3,[DueDate])

    >>
    >> Using the other code, if you are in December and have a due date of
    >> January of the next year it would not give the proper compliance date.
    >> Or if the due date is November of last year it would also not give the
    >> proper compliance date - it would say Feb of this year instead of
    >> December 31 of last year.
    >>
    >> Ron
    >>
    >> BruceM wrote:
    >>> As a variation of an earlier suggestion, this could be a calculated
    >>> query
    >>> field:
    >>> ComplianceDate: IIf(Year(DateAdd("m",3,[DueDate])) >
    >>> Year(Date()),DateSerial(Year(Date()),12,31),DateAdd("m",3,[DueDate])
    >>>

    >>

    >
    >
     

Share This Page