Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculating Current Year (2000)

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

    =DSUM("ADVERTISINGDOLLARS","Tablename","Year(DateF ield) = Year(Date())")

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

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculating Current Year (2000)

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

    =DSUM("ADVERTISINGDOLLARS","Tablename","Year(DateF ield) = Year(Date())")

    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
  •