Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation on a field (2003)

    Hello,

    How can I make a macro or a formula that if an employee puts in an amount in a field, it would subtracted from a previous advancement. For example, I have a three fields-Budget, Advancement, invoice1, if there is an amount in the advancement, I want what is enter in the invoice1 field to be reduce for the advancement and pay only the difference. Here is an example,

    Budget $50
    Advancement $25
    Invoice1 $35
    NewInvoice1 amount is $10 which is the difference. Basically I want the user to input the number and before they go to the next field have it do the calculation in the same field Invoice1. I hope this make sense. I am new to Access so any help would be great. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation on a field (2003)

    I'm not in favor of making such a calculation fully automatic. If the user makes a mistake and corrects the amount, that would lead to the new value being subtracted again.

    My preferred method would be to store each amount in a separate record in a different table. That way, you keep a history of the amounts, and you can calculate the current situation at any moment in a query, or you can easily roll back if necessary.

    If that is not feasible or desirable, I'd put a command button on the form next to the text box in which the amount is entered. The user can enter, and if necessary correct the number, then click the command button. This would perform the calculations and update the fields, using a bit of VBA code.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on a field (2003)

    ok. I will try making a separate record in a different table and see if that works. Thanks. gb

Posting Permissions

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