Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Field (2003)

    I have a report that builds from a query. I've got a particular section in my report which returns the year and the assessment fields from my query. So on the form it looks something like this...

    YEAR ASSESSMENT
    2002 154.00
    2003 238.23
    2004 34.33
    2005 4.34

    I have another field called YTDAssessment. Is it possible to write some kind of function in the Assessment field to return the YTDAssessment field versus the Assessment field if the year field is equal to 2005? I know I can do this in Web programming, but not sure how to with Access. Any help would be much appreciated. Thanks beforehand!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    Could you provide some more information, please? Where does the field YTDAssessment come from? Is it in one of the tables that contribute to the query you mention, or in another table?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    I apologize, it is in the same query.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    You could create a calculated column

    ThisYear: IIf([Year] = Year(Date()),[YTDAssessment],Null)

    or if you prefer to combine Assessment and YTDAssessment in one field:

    ThisYear: IIf([Year] = Year(Date()),[YTDAssessment] & " vs " & [Assessment],Null)

    or something like that - adapt to your needs.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    Hi Hans,

    I gave the second option a try. I'm getting the following message...

    The expression you entered has a function containing the wrong number of arguments.

    I inserted the following in my report...

    =IIf([Year] = Year(Date(2005)),[YTDAssessment] & " vs " & [Assessment],Null)

    Any thoughts? Much thanks beforehand for all of your help!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    Just remove the 2005, as in my previous reply.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    I'm the worst explainer. Let me try to explain this using some generic data...

    I've got four columns in my query that is returned...

    1. Item
    2. Year
    3. Price
    4. Interest

    I have a report which calls on the query to display my results for only three fields (Item, Year, and Price)...

    Eggs, 2004, 3.99
    Ham, 2005, 4.99

    Now my query returns the following (notice the interest column)...
    Item, Year, Price, Interest
    Eggs,2004,3.99,1.00
    Ham,2005,4.99,.55

    So in my report, I have three field names in Item, Year, and Price. But for the price field, I'd like to replace the field name with an expression that says if the year for some reason is 2005 display the interest in place of the price, otherwise display price for all other years. Does that help any?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    Do you want to specify the year 2005 explicitly, or do you want it to be the current year (now 2005, next year 2006)?

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    2005 Explicitly.

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

    Re: Report Field (2003)

    What exactly do you want to show on your report given your example?

    Also why don't you show your query here.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Report Field (2003)

    Try this in the control source of the report control:

    =IIf([Year] = 2005,[Interest],[price])
    Regards
    John



  12. #12
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    That almost got it...

    =IIf([Year] = 2005,[Interest],[price])

    Except, if the year isn't 2005 it's shows an error. But if it's 2005, it's displaying the correct data. Additionally, I'm receiving the following error in the expression:

    This control has a reference to itself.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    You should give this text box a different name than Year, Interest or Price.

  14. #14
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Field (2003)

    First off, thank you everyone who has provided input for a solution. It's great to know there are people out there who are willing to help. Thanks again.

    Hans,
    I tried to gice it a different name, and now when i run the report, it prompts me for a value to enter.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Field (2003)

    What is the name you are prompted to enter a value for?

Page 1 of 2 12 LastLast

Posting Permissions

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