Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2005, 06:05 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default Auto Calculated Field in Access

hi to all,

I want to create an auto calculated field in my Access DB as follows

ClosingBalance=OpeningBalance+Installment

OpeningBalance And Installment are two other fields in my table.
How do i set the default value in the ClosingBalance field based on this formula?

Anubhav Kumar
__________________
Anubhav Kumar
http://anubhavtimepass.blogspot.com/
 
Old March 15th, 2005, 08:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   The rule of thumb is that you don't store calculated fields in a table, only in a query, or on a form or report. Since the values are calculated dynamically, there is no need to store the data.

   That said, to put this in a query, you would create a query with the table in it, and then use this formula in the query Field: box

ClosingBalance:([OpeningBalance] + [Installment])

   To put this in a form or report, create an unbound text box, and then put this formula in the text box in design view:

Report
=Sum([OpeningBalance] + [Installment])

Form I think would be:
=Sum(Me.OpeningBalance + Me.Installment)

   I would have to play around with that syntax to make sure it was right for the Form.

   I am not sure why you would want to set a default value on your closing balance, since it would be calculated in the query. But to do that in a table, you open the table designer, select the field, and put in a Default Value:

HTH


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping with calculated field in report jack123 Access 3 August 9th, 2007 06:46 AM
Calculated field total in cross-tab ladag Crystal Reports 3 October 6th, 2006 01:40 AM
How to create a Calculated Field fdtoo SQL Server 2000 0 April 11th, 2006 08:34 PM
Automatically select value from a calculated field jimmy0305 Access 0 June 21st, 2005 10:43 AM
Validate calculated field in sub form MG76 Access 4 March 18th, 2004 04:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.