Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    29

    Unanswered: Date Calculations On Subform-MS Access2000

    My application has got a mainform and a subform....I have date fields on both forms .I want a date field on a subform to do calculations based on a date field on the main form .The scenario is as follows;
    Field(s)on the main form : PURCHASE_DATE,FREQUENCY
    Field(s) on the subform: EXPECTED_PAYMENT_DATE
    The Repayment Schedule should be autogenerated on Subform as follows;
    The first installment date(EXPECTED_PAYMENT_DATE)should be six months after the PURCHASE_DATE,thereafter the Payment Schedule should be at 6 months interval depends on FREQUENCY chosen/entered.
    Your suggestons will be highly appreciated!!!
    Last edited by fadone; 11-21-05 at 04:07.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can access the values in the 'other' form using forms!<myformname>!<mycontrol>

    you can acess the chold form using the control name

  3. #3
    Join Date
    Nov 2005
    Posts
    29
    Quote Originally Posted by healdem
    you can access the values in the 'other' form using forms!<myformname>!<mycontrol>

    you can acess the chold form using the control name
    Well,I have implemented your solution as you can see below ,but couldnot get through;an expression was defined at control source property of EXPECTED_PAYMENT_DATE control on the subform
    Form name :LongTermLoan
    Subform name:LongTermLoanInterest

    =DateAdd("m",6,[Forms]![LongTermLoan]![DATE_OF_PURCHASE])
    Here my intention was to get the following date after 6months from DATE_OF_PURCHASE,but instead I got #Name?
    Please help.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    =DateAdd("m",6,[Forms]![LongTermLoan]![DATE_OF_PURCHASE])
    The syntax looks correct - the value in the [DATE_OF_PURCHASE] control is probably not a valid date (it's probably null).

    Are you familiar with using code? If so, put an afterupdate event on the [DATE_OF_PURCHASE] control that places the date value into a date/time variable and use the function to put the new value in the destination control.

    This will allow you to catch invalid date values (such as Null which is generally the default value of a control when a form opens).

    You could avoid code by using a hidden control that has:
    Code:
    =Iif(Len([DATE_OF_PURCHASE] & "") = 0,Now(),[DATE_OF_PURCHASE])
    and reference your function t0 this control. If you require [DATE_OF_PURCHASE] to be a date value, it shouldn't crash because [DATE_OF_PURCHASE] will either be a valid date or empty.

    Good luck
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Nov 2005
    Posts
    29

    Autogenerate Payment Dates On Subform

    Thanks for the reply.An expression worked fine after debugging it...I could get the EXPECTED_PAYMENT_DATE six months after DATE_OF_PURCHASE...BUT now the problem here is how to generate dates at six months interval from the resulted EXPECTED_PAYMENT_DATE ie the difference between the EXPECTED_PAYMENT_DATE should be six months....

  6. #6
    Join Date
    Nov 2005
    Posts
    29

    Autogenerate Payment Dates On Subform

    Thanks for the reply.An expression worked fine after debugging it...I could get the EXPECTED_PAYMENT_DATE six months after DATE_OF_PURCHASE...BUT now the problem here is how to generate dates at six months interval from the resulted EXPECTED_PAYMENT_DATE ie the difference between the EXPECTED_PAYMENT_DATE should be six months....

  7. #7
    Join Date
    Nov 2005
    Posts
    29

    Autogenerate Payment Dates On Subform

    Thanks for the reply.An expression worked fine after debugging it...I could get the EXPECTED_PAYMENT_DATE six months after DATE_OF_PURCHASE...BUT now the problem here is how to generate dates at six months interval from the resulted EXPECTED_PAYMENT_DATE ie the difference between the EXPECTED_PAYMENT_DATE should be six months....

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    come on now, we can't do everything for you!

    If you want to add six months to a value, use the same syntax. If you are adding six months to a value that added six months to something else, then you can also add 12 months to the original value.

    Sketch on the back of a napkin what you want to do and run with it .....

Posting Permissions

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