[thelist] MS Access: Creating a default calculated field?

[Phil Turmel]

Frank wrote:
> 
> I want to whip up a quick database, where one of the fields has a 
> default that is a calculation, or that the calculation is automatically 
> triggered when data is entered.
> 
> I keep trying variations of this
> 
>    Balance =Sum(['RentRoll.Due']-['RentRoll.Discount']-['RentRoll.Paid'])
> 
> But Access keeps telling me that it can't find the fields. Can someone 
> suggest how I can go about doing this? I've used Access a little bit for 
> web-stuff years ago, but I want to use this as a stand alone database 
> that I can ship off to someone.

Field syntax in Access would be [RentRoll].[Due].  You can omit the 
brackets in SQL if there are no spaces in each piece of the table or 
field name.  The Sum Function is for computing across rows, not within a 
row.

Access doesn't have triggers, so you're going to have great difficulty 
including such a field in a table.  Given a table named RentRoll with 
the source fields, create a saved Query (=View in other databases) with 
SQL like so:

SELECT RentRoll.*, Due-Discount-Paid AS Balance
   FROM RentRoll;

Then use this saved query in place of the table.

> 
> Fundamentally, I want to use this as a more flexible spreadsheet.

You can open the query and it will update the Balance column like a 
spreadsheet.  If you want more control, you probably ought to use the 
base RentRoll table on a form, plus this calculated field.  Set the 
calculated field Enabled=true, Locked=true.  Then set the form to 
datasheet view only.

Hope this helps get you started.

Phil