Calucuating a field.

[diazr]

Can someone help me construct a formula in a form that I can incorporate into the control source in a field. The primary fields in my form that I need to evaluate for the formula are [Date], [Account] & [Amount].

I would like the field to add the sum of [Amount] for all [Account] numbers that are the same for a particular [Date] range. I'm a newbie to Access so any suggestions would be appreciated.

Thank you!!!!

 

[sbenj69]

First of all, let me start by saying "Date" is a reserved word, do not name fields the same as reserved words.

The easiest way would be to make a query with parameter criteria:
Assuming 3 fields: MyDate, Acct, Amount
(In the criteria for mydate): Between [Starting] And [Ending]

Then base a form on this query, make it continuous. In the footer of the continuous make an unbound textbox with this as control source:
=Sum([Amount])

That's the easy way..... a better looking way:

Same query, except, for the criteria, put this:
Between Forms!form1.txtstart And Forms!form1.txtend

Create a blank form in design view. Add 2 unbound textboxes (txtstart and txtend) and a button with no function (name it commandfind)

Using the subform wizard, create a subform off the query. Name it Subform1. In the footer of the subform make an unbound textbox and name it txtMySubtotal, and set it's control source to:
=Sum([Amount])

Then, in the main form section, add an unbound textbox, and set it's control source to:
=Subform1.Form!txtMySubtotal

Finally, we have to add functionality to the button, so in the properties go to the event tab, and locate "On Click".

Add code here:
Me.SubForm1.ReQuery

Here is a sample showing the latter method:

 

[maxmangion]

Can someone help me construct a formula in a form that I can incorporate into the control source in a field. The primary fields in my form that I need to evaluate for the formula are [Date], [Account] & [Amount].

make sure you change your field name "Date" as you will run into problems later since the word date is the name of a function.

 

[diazr]

Calculation...

Thanks, sbenj69... Im trying to do something slight different I might of explained it wrong. Im attaching a small sample of the MDB so you can see what Im tring to acheive.

When a record is displayed the field [Amount_Previous_yr] should display the total of amounts that were entered for that particular [Account] being displayed. So in other words if im looking at a record and the Account is 33012170 the field [Amount_Previous_yr] should give me a total of all the transacions that were entered under that account for the year 2007.

 

[sbenj69]

will it always be "Last Year"?

I'm assuming that you are wanting grouping by customer name and just one record to show the total, correct?

 

[diazr]

The year will be always the previous so in 2009 it will be adding up the totals for 2008 etc. The key field is [Account] so the total should be grouped by account giving the total cash for that particular account for the given year.

 

[sbenj69]

Ok, I think I'm tracking in on what you're trying to do; well sort of.

Let me start off by saying, storing calculated values in a table is a big "NO NO!" Calculations should be done at the time of render, meaning, the calculation is done exactly when it needs to be displayed.

Ok, now that that's said.... I simply made a second query that totalled up last year, and brought that query into your original query related by account number. I also made the original query only pull up this year's transactions.

I think this is closer to what you were wanting...... I don't know if you were needing a total for this year as well.

Here is your updated sample:

 

[diazr]

Calculation

This is excellant! sbenj69 its exactly what I needed.. I got the picture now by looking what you did with the queries and not trying to do it in the form. Thank you so much for your help, now that I have jumpted this obstacle I can continue. Thank you again!