My BALANCE field is not being saved

Posted on 2003-03-30
Medium Priority
Last Modified: 2008-07-03
Hello and thanks for the assistance -

I have three Access fields:

* VisitCost
* VisitPayments
* VisitBalance

The control field for the VisitBalance is =[VisitCost]-[VisitPayments], which gives me the difference between the two fields. The problem is that the VisitBalance isn't saved, so I can't run reports off of my VisitBalance field.

I know the problem is because I'm using =[VisitCost]-[VisitPayments] in the control field.

How can I do the following:
* Get the difference between my VisitCost and VisitPayments field to default  on my FORM and
* Store the difference in my VisitBalance field for reports

Any help is appreciated - thanks

Note: I tried putting =[VisitCost]-[VisitPayments] as a default on the VisitBalance field, but it didn't work for me.
Question by:GMORRIS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8234580
Generally speaking, you may not want to save calculated fields.  However, sometimes it is valid.

"...=[VisitCost]-[VisitPayments] as a default on the VisitBalance field..."

will never work if you are trying this in your table design.

To make your report work, add an unbound field on the report and include the same calculation as in your form.

to save the value (VisitBalance) in your table, you must run an update query

In the query, make an expression in the Update To row =[VisitCost]-[VisitPayments]
and in the criteria row use your builder to specify

I can be more specific if you provide form names etc.

You are very close to done.

Author Comment

ID: 8234621
dotthei -

Thanks for the info.  My database is still in the design phase.  The balance field is the last issue.  I do want to save the calculated field of Balance if possible

The reason being:  I want to be able to run a report that finds all balances > 0.00

Is this possible?  I understand the report option you gave me using an unbound field.  Could I add an IF statment such as if(balance>0, add the record to the report, ELSE don't add the record to the report?

My choice would be to store the balance, though.  Thanks

And I'm in the middle of Central America helping a clinic w/ their database (I'm teaching myself Access), so I'm not privy to a lot of info to help me with problems so I appreciate your help.

Accepted Solution

dotthei earned 800 total points
ID: 8234795
Thank you for your efforts in Central America - one day we will rid the world of the "ugly American".

You can run your report to show balance=0.00 without storing the value - but let's not get of track, let's solve your challenge as you see it.  The only valid solution is the one that fits within your current understanding.

Store the balance is OK, but you will need to update the field once the VisitCost and VisitPayments fields are populated.  We will address that soon.

You have many options how to report on Balance = 0 or Balance > 0.  Your report has a Recordsource, and this is probably a query or a SQL string (could be a table, not likely).  You can hardcode the Balance filter, or you can use a parameter - I recommend parameter.

For hardcode of balance=0.00
In your Report properties, Data Tab, Recordsource
click the ellipse ...
brings up a query design grid
In your query, in the balance field, in the criteria row,

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Expert Comment

ID: 8234804
not =0
but just
LVL 41

Expert Comment

ID: 8234850
Rather than using a stored field or an unbound field on your report, use a calculated field in a query (this is what dotthei is getting at, I think).

Create a new query and base it on your table. Select all the fields you need. In the next blank column, in the Field row, put:
Balance: [VisitCost]-[VisitPayments]

When you run the query, it will have an extra column showing the balance. In all important respects, this query is exactly like your table, in that you can filter it, base reports on it, edit it (except for the calculated field) etc. You can filter on the Balance field just by setting a criteria on it, or using the WHERE parameter when you open a report.

LVL 54

Expert Comment

ID: 8234859
Hmm, storing the VisitBalance is a bad idea.

Normally an application will have a separate table with payments made and these are manually assigned to the costs.

There's always the trouble of people only paying part of a bill or e.g. "one and a half" bill or even too much....

Personally I have the user mark the payed Visits and making your VisitPayments equal to VisitCost could be such a mark.
For a partial payed visit the VisitPayments will be less then the VisitCost and thus the selection can be like:
select VisitCost, VisitPayments from tblVisits WHERE VisitCost > VisitPayments;

For your report you can add the VisitBalance in a query like:
select [VisitCost]-[VisitPayments] as VisitBalance from tblVisits;

Thus you can use the field "directly" on the report and even post a =SUM(VisitBalance) field in the footer for the total.


LVL 18

Expert Comment

ID: 9247115
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to dotthei
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question