# Thread: Calculating Current Year (2000)

1. ## Calculating Current Year (2000)

I have a form in which I would like to calculate 1) all advertising dollars and 2) advertising dollars for just the current year. Any ideas what the control source would be? I have =Sum([ADVERTISINGDOLLARS]) for the first calculation. The form and query that I am basing this on has a date field and then a year field (based on the date field - formatted "yyyy".

Any help would be greatly appreciated!

2. ## Re: Calculating Current Year (2000)

You can use the formula Year(Date()) to get the current year, and put that in the criteria field for year. Hopefully that will do the trick.

3. ## Re: Calculating Current Year (2000)

how do i combine that with the AdvertisingDollars to get sum for the year - something like =sum(AdvertisingDollars) year(date())

4. ## Re: Calculating Current Year (2000)

You can use what is called a Group By query to do the sum in one tidy query. To do that, open your query in design mode and click on the Menu Item View and select Totals. That will change the query in your query grid to include a new row that will say Group By for each field selected. Change the Group By for Advertising Dollars to Sum. Then put the criteria Year(Date()) in the field formatted as Year only, and you should get one record that is the sum. (That assumes you don't have any other fields involved.) If you have other fields, it will give you the unique combinations and the sum for each. If you don't want to see the year returned, you can change its Group By to a Where. Hope this is clear.

5. ## Re: Calculating Current Year (2000)

If you have a form in which you would like to show this sum then use

You will have to fill in your Tablename, DateField.
HTH
Pat

6. ## Re: Calculating Current Year (2000)

Looks good so far if i use the expression as a control source of a text box in the form footer - except results are for all records in table. I would like the results on the form to be specifically for that record (with subform).

thanks!

7. ## Re: Calculating Current Year (2000)

You will have to add to the criteria of the DSum command.
You're command

would become something like

=DSUM("ADVERTISINGDOLLARS","Tablename","Year(DateF ield) = Year(Date()) AND FieldinTable = Forms![yourformname]!FormFieldname")

HTH
Pat

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•