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

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,

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.


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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

578 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