Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Controls (Access 2003)

    I have a Invoice Entry form with 4 Subforms; When I print the Invoice Report also with 4 SubReports, I get error messages on totals where there is no entry. I am sure that it is my lack of knowledge which is causing this. When I have entries in all of my subforms, I get no error messages; or if I enter 0 and $0.00 then I do not get an error message in my totals; however the underlying table has a 0 for an entry which is something I wish to avoid. Maybe I need to enter an Iff statement, however I do need to enter one which will when "Y" for warranty is entered it will return 0 for a total. Can any help me figure out why I am getting an error on the totals in my Invocie Report when there is no entry in a Subform? Thank you, I am in the midst of losing whatever it was I knew. Delta

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

    Re: Calculated Controls (Access 2003)

    Do yo want to refer on the main report to totals on the subreports? If so, try something like

    =IIf([SubReportName].Report.HasData,[SubReportName]![txtTotal],0)

    This checks if the subreport named SubReportName has data. If so, it returns the value of the text box txtTotal from the subreport, otherwise, it returns 0.

  3. #3
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    Thank you Hans, that worked great. I have been going through the books I have and am unable to find how to add an "or IIf" to state IIF Warranty = Y then 0. Warranty is in the same subreport as the first IIf Statement The IIf statement took out my totals on the main Report which totals from the four subreports, but I will deal with that after I get the rest of this working. I am new at this and trying to take it slow. Thanks again. Delta

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

    Re: Calculated Controls (Access 2003)

    Not sure what you mean, but you can do things like this:

    <code>=IIf((Condition A) Or (Condition [img]/forums/images/smilies/cool.gif[/img], Value_if_A_or_B, Value_if_Otherwise)</code>

    and

    <code>=IIf(Condition A, IIf(Condition B, Value_if_B, Value_if_Not_[img]/forums/images/smilies/cool.gif[/img], Value_if_Not_A)</code>

  5. #5
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    =IIF([sbrpt Sublet].Report.HasData, or [sbrpt Sublet].Report![Warranty]=y,[sbrpt Sbulet]![Sublet Cost],0)
    If Subreport Sublet has Data or Subreport Warranty = y, then Sublet Cost is 0

    This of course is not working, but is close to what I want to say. Warranty is a field in the sbrpt Sublet
    Thanks for your help
    Delta

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

    Re: Calculated Controls (Access 2003)

    Try this:

    <code>=IIf([sbrpt Sublet].Report.HasData, IIf([sbrpt Sublet]![Warranty]="y", [sbrpt Sublet]![Sublet Cost], 0), 0)</code>

    I have assumed that the value y is a text value, so I enclosed it in quotes.

  7. #7
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    Hi Hans, I have tried your Iff statement, and Yes the "Y" is a text field, however I keep coming up with an error. I just can't figure out what I am missing. If you have any words of wisdom I would greatly appreciate them.
    delta

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

    Re: Calculated Controls (Access 2003)

    Check carefully that all names used in the expression are correct; the simplest typo will cause an error. In particular, you must use the name of the subreport as a control on the main report. This is not necessarily the same as the name of the subreport in the database window. You can see the control name by opening the main report in design view and clicking once on the subreport. (Don't click on it a second time, you will select something on the subreport then.) The control name will be displayed in the Controls dropdown box on the Formatting toolbar, and in the caption of the Properties window.

  9. #9
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    Once again, you came through, I had a typo, and even my calculations are working on the main form. I really appreciate all of your help. Delta

  10. #10
    Lounger
    Join Date
    Jan 2004
    Location
    Honolulu, Hawaii, USA
    Posts
    47
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    Used to be that you could use a PUBLIC variable from the MODULE of a FORM or REPORT as a component of a calculated control. What gives? Access now reports #name?.
    Are we having yet another Holy War over what is safe and unsafe, or was this an oversight during yet another re-write?

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

    Re: Calculated Controls (Access 2003)

    What are you ranting about? As far as I know, it has never been possible to refer directly to VBA variables in expressions. You can create a custom function to get the value of a variable, and use that in expressions.

  12. #12
    Lounger
    Join Date
    Jan 2004
    Location
    Honolulu, Hawaii, USA
    Posts
    47
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculated Controls (Access 2003)

    Figured a quick fix to the problem.
    Add a simple PRIVATE FUNCTION to the MODULE which returns the value of the variable.

    Private sgValue As String
    Private Function getValue()
    getValue = sgValue
    End Function

Posting Permissions

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