Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Question Unanswered: Date calculation

    I need to build a function to auto calculated some dates.
    For example:
    Date5 is 11 months after Date2. And Date5 will auto-filled once Date2 is entered. The other condition is, if Date2 is "Blank" then Date5 should be calculated from Date1. Date1 or Date2 is entered manually.

    Can any expert help me please.

  2. #2
    Join Date
    Nov 2004
    Posts
    22
    Quote Originally Posted by ggo
    I need to build a function to auto calculated some dates.
    For example:
    Date5 is 11 months after Date2. And Date5 will auto-filled once Date2 is entered. The other condition is, if Date2 is "Blank" then Date5 should be calculated from Date1. Date1 or Date2 is entered manually.

    Can any expert help me please.

    You should be able to do it as an After Update event on Date5 using a conditional IIF statement. Something like this:

    IF [Date2] Is Null Then
    [Date5] = [Date1] + 330
    Else
    [Date5] = [Date2] + 330
    End If


    What this statement does is check to see if Date2 has a value in it. If it doesn't, then it sets Date5 as Date1 plus 330 days (rougly 11 months...I don't know how to advance by the number of months, there may be another trick to this). If Date2 does have a value, then it sets Date5 as Date2 plus 330 days.

    I may have the syntax wrong, but the general concept should work.

    I hope this helps.

    Craig
    rockjock

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    thanks
    I'll try that now.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not sure that "afterupdate" is the correct event to locate this code, it'll work, bt you will ofrce a second update of the data, and yourun the risk that the record may not be saved if the users closes the form without saving or movin off the record.

    I think youmay need toconsoder putting a call to the function in any or all of the following
    the controls (date1,date2) "lostfocus", "beforeupdate" event. the reason
    the "beforeupdate" will ensure "good" data is written to the table
    the "lostfocus" will set date5 whilst the user is still entering data, and should provice positive feedback
    Is Date5 a strored (ie bound) field, ie is it a column in the underlying record source, if not you need to place a call to the function in the forms "current" event (trapping for null records first that is)
    As a final comment I owuld suggestyouconsoder changing the names of the controls. Date1,Date2 & Date5 are pretty meaningless, unless you intended to mask it from the forum. Better than text101, text102 and text105 I'll grant you but could I suggest instead InvoiceDate, OrderDate, DeliveryDate (or DateInvoice,... or tbInvoiceDate,...)

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Or use the DateAdd Funcktion like that
    DateAdd("m", 11, date2)

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Quote Originally Posted by ggo
    I need to build a function to auto calculated some dates.
    if Date2 is "Blank" then Date5 should be calculated from Date1.
    Also try:
    date5=iif(date2="", DateAdd("m", 11, date1), DateAdd("m", 11, date2))
    OR
    date5=iif(if IsNull(Date2), DateAdd("m", 11, date1), DateAdd("m", 11, date2))

  7. #7
    Join Date
    Nov 2004
    Posts
    4
    HI hammbakka,
    you are correct. The if function didn't performed as I expected. Date1, date2 etc are the examples in my question. They real dates are as Injury date, Incapacity Date and Due Date.
    The problem I will have with your suggestion is that Injury Date ( i.e. Date1 ) is the data from a linked table. So, the Due Date is calculated from the Injury Date field that's already populated. I know Excel can calculated this without problem, I'd tried several IF functions, but none worked. However, I'll try your suggestion today.
    I
    ll keep you inform. Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •