Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Implementing Math Formula

  1. #1
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75

    Implementing Math Formula

    Hi, how to implement a math formula involving 4 item. The column in wat i am doing are Current balance, receive, issue out, new balance. The formula would be current balance add receive minus issue out and equal to new balance. How and where should i put this formula.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    If this is in a table then you should *not* do it there. I would do it is a query of the table.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    Actually, i'm not doing it in a table. I'm doing it in a form, whereby when i enter a value into the field mention then the calculation will be executed and the both the value i type in and the final answer will also appear at the table. The form is where the value is type in and the table is just a record only. That why i not sure how to implement the formula.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    As I said, it is not wise to store calculated values in a table. You can do the calculation on a form or in a query but why do you feel you need to keep the results in a table?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    jbarrum is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Apr 2009
    Location
    Houston, Tx
    Posts
    164
    Are you storing the in/out items in the same field as positive/negative numbers? If I think you could get what you want from using sum function in an unbound control on your form

  6. #6
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    The data is store in four field in the form "Current balance, receive, issue and new balance". So i need to create a math formula in the form where Current balance + Receive - Issue = New balance. All the value for Current balance, receive and issue will be enter in the form and the New balance will appear on the New balance field after the calculation.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    Quote Originally Posted by cwwaicw311 View Post
    The data is store in four field in the form "Current balance, receive, issue and new balance".
    So the first three pieces of data are not in a table, right?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    Yup, the first 3 data is not in the table. The 3 data will straight enter into the form. Then after the data is enter the math calculation will automatically perform n show the final value in the New balance field.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    So the ControlSource of the NewBalance control will be:
    =Nz(Me.[Current balance],0) + Nz(Me.Receive,0) - Nz(Me.Issue,0)
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    I try the formula already, but it is not working and on my new balance field before i type in anything there appear a word like tis " #Name? "

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    What are the actual names of the three controls? It is the 1st row on the Other tab of the property sheet for each control.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  12. #12
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    I change all 3 control into CurrentBalance, Receive and Issue and also use the same name on the formula u sent me. But still got the #Name? on the NewBalance field.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    Maybe you need to post the db, or at least a sample of it with the problem.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  14. #14
    cwwaicw311 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    75
    This is the Access file which i am doing. Maybe you can take a look at it to see what is the problem. Thank.
    Attached Files Attached Files

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,757
    Try this one. I removed the Me. from the formula.
    Attached Files Attached Files
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Help with formula for Query
    By goldie6175 in forum Queries
    Replies: 1
    Last Post: 02-18-2010, 09:29 AM
  2. Concatenate Formula
    By Shingo in forum Programming
    Replies: 1
    Last Post: 07-25-2009, 08:14 AM
  3. simple math coding for form field??
    By RCBNewbee in forum Programming
    Replies: 7
    Last Post: 07-13-2009, 07:30 AM
  4. Math problem
    By kbrodrick in forum Programming
    Replies: 3
    Last Post: 05-06-2009, 07:56 AM
  5. formula on report??
    By dike969 in forum Access
    Replies: 0
    Last Post: 03-04-2007, 11:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums