Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I sub-total only a specific area within a report

Discussion in 'Information Technology' started by Determined_Jim, Oct 31, 2005.

  1. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    I am attempting to produce a report with detail amounts. I need to add a
    sub-total after the detail line showing acct # 201l.

    Example
    acct# Amount
    101 5.00
    201 3.00
    sub 8.00
    301 6.00
    401 7.00

    How do I accomplish this. I have been to my manual but have found confusion

    Jim
     
  2. Loading...

    Similar Threads Forum Date
    Is Sikhism a Universal Religion or a Culturally Specific One? Sikh Sikhi Sikhism Feb 22, 2010

  3. Andy Couch - UK Access User Group

    Guest

    Jim, sub-totals are easiest to produce when you have something to group by,
    for example if the first two records are for one month and the second two are
    for the next month. The you can group by month and sub-total.

    What is you criteria for generating a sub-total.
     
  4. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    >> sub-total after the detail line showing acct # 201l.

    I don't see any account # of 2011 in the example data? Am I missing
    something here..this is kind of confusing?

    > acct# Amount
    > 101 5.00
    > 201 3.00
    > sub 8.00
    > 301 6.00
    > 401 7.00
    >
    > How do I accomplish this. I have been to my manual but have found
    > confusion


    What about the 301, and the 401...do you wan a sub-total for that? I can't
    see any rhyme or reasons as to they you got a sub total for the first two,
    but not the 2nd set of numbers?

    You going to have to clear up your example here, as I can see no pattern, or
    reason to how you want to group this data.

    I suppose a possible solution here is to have a table with the list of
    accounts, and a grouping

    account# ReprotGrouping
    101 5
    201 5
    301 10
    410 15


    So, you could do a sql join on the above table (based on the account
    number), and then use the reports sorting and grouping.

    If there is some other approach to how you "group" your data, you might want
    to give further details here, since as it is now, we are only making WILD
    guesses as to how you group this data. Do you want to use the first digit of
    the account number? Or, are we talking about a particular "range" of
    accounts
    that need to be grouped together? Perhaps you wan to group by the last two
    digits. Your above example does group the data by the last two digits...so
    perhaps that is it???? If yes, then make a expression in the query builder
    like

    lasttwoDigits:right([accountNumber],2)

    Actually, you might give a few more details, as I
    am just guessing as to how you want to group here, but the above should give
    you some starting ideas...


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  5. Ofer

    Ofer
    Expand Collapse
    Guest

    I also can't see the logic behind what you are trying to do, but a way that
    you can achieve that

    1. In the detail section create another text box under the record list, that
    will display the sub-total, set the property "Can shrink" of this text box
    and the Detail section to Yes

    2. On the On format event of the detail section write the code

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    MyCount = MyCount + Me.Amount
    If Me.[acct#] = 201 Then
    Me.SumFieldname.Visible = True
    Me.SumFieldname= MyCount
    MyCount = 0
    Else
    Me.SumFieldname.Visible = False
    End If

    End Sub
    3. Declair the MyAmount in the decleration of the report

    Option Compare Database
    Dim MyCount As Double
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Determined_Jim" wrote:

    > I am attempting to produce a report with detail amounts. I need to add a
    > sub-total after the detail line showing acct # 201l.
    >
    > Example
    > acct# Amount
    > 101 5.00
    > 201 3.00
    > sub 8.00
    > 301 6.00
    > 401 7.00
    >
    > How do I accomplish this. I have been to my manual but have found confusion
    >
    > Jim
     
  6. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Andy
    I am amazed with what can be done with Access and equally amazed with what
    is difficult to do with Access. Having dealt with end-users I know the
    pit-falls. I can group the acct #s into a table and lay the responsibilities
    off to the using community to update this table however I chose to take on
    the responsibility of changing the highest acct # in the group myself. Coming
    from technology prior to contemporary PC languages I thought it would be
    simple to add a report sub-total (ha-ha-ha). At this point I am undecided how
    I want to handle this challenge
    --
    Another Learning Experience -- Jim


    "Andy Couch - UK Access User Group" wrote:

    > Jim, sub-totals are easiest to produce when you have something to group by,
    > for example if the first two records are for one month and the second two are
    > for the next month. The you can group by month and sub-total.
    >
    > What is you criteria for generating a sub-total.
    >
     
  7. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Albert
    I missed a typo in my request with acct # 201|. It should have been 201. The
    acct #s can be grouped however I know how an end-user community can respond
    to maintenance and chose to take on the responsibility of instituting change
    myself. I am from a technology prior to PC's in which one could add a
    sub-total to a report rather easily. J am amazed with what one can do with
    Access and With one can't do with Access easily. I am leaning toward behind
    the scene VB code however this is still undecided since I am gaining speed in
    Access
    --
    Jim


    "Albert D.Kallal" wrote:

    > >> sub-total after the detail line showing acct # 201l.

    >
    > I don't see any account # of 2011 in the example data? Am I missing
    > something here..this is kind of confusing?
    >
    > > acct# Amount
    > > 101 5.00
    > > 201 3.00
    > > sub 8.00
    > > 301 6.00
    > > 401 7.00
    > >
    > > How do I accomplish this. I have been to my manual but have found
    > > confusion

    >
    > What about the 301, and the 401...do you wan a sub-total for that? I can't
    > see any rhyme or reasons as to they you got a sub total for the first two,
    > but not the 2nd set of numbers?
    >
    > You going to have to clear up your example here, as I can see no pattern, or
    > reason to how you want to group this data.
    >
    > I suppose a possible solution here is to have a table with the list of
    > accounts, and a grouping
    >
    > account# ReprotGrouping
    > 101 5
    > 201 5
    > 301 10
    > 410 15
    >
    >
    > So, you could do a sql join on the above table (based on the account
    > number), and then use the reports sorting and grouping.
    >
    > If there is some other approach to how you "group" your data, you might want
    > to give further details here, since as it is now, we are only making WILD
    > guesses as to how you group this data. Do you want to use the first digit of
    > the account number? Or, are we talking about a particular "range" of
    > accounts
    > that need to be grouped together? Perhaps you wan to group by the last two
    > digits. Your above example does group the data by the last two digits...so
    > perhaps that is it???? If yes, then make a expression in the query builder
    > like
    >
    > lasttwoDigits:right([accountNumber],2)
    >
    > Actually, you might give a few more details, as I
    > am just guessing as to how you want to group here, but the above should give
    > you some starting ideas...
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
    >
     
  8. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Ofer
    There was a time when one could add a sub-total field to a report easily,
    priot to PC languages. I chose this approach with concerns of end-users
    forgetting to perform maintenance

    I coded as you described however I got an erro: You can't assign a value to
    this object Me.subfieldname = Myaccount. Do you have any suggestions?
    --
    Jim


    "Ofer" wrote:

    > I also can't see the logic behind what you are trying to do, but a way that
    > you can achieve that
    >
    > 1. In the detail section create another text box under the record list, that
    > will display the sub-total, set the property "Can shrink" of this text box
    > and the Detail section to Yes
    >
    > 2. On the On format event of the detail section write the code
    >
    > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > MyCount = MyCount + Me.Amount
    > If Me.[acct#] = 201 Then
    > Me.SumFieldname.Visible = True
    > Me.SumFieldname= MyCount
    > MyCount = 0
    > Else
    > Me.SumFieldname.Visible = False
    > End If
    >
    > End Sub
    > 3. Declair the MyAmount in the decleration of the report
    >
    > Option Compare Database
    > Dim MyCount As Double
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "Determined_Jim" wrote:
    >
    > > I am attempting to produce a report with detail amounts. I need to add a
    > > sub-total after the detail line showing acct # 201l.
    > >
    > > Example
    > > acct# Amount
    > > 101 5.00
    > > 201 3.00
    > > sub 8.00
    > > 301 6.00
    > > 401 7.00
    > >
    > > How do I accomplish this. I have been to my manual but have found confusion
    > >
    > > Jim
     
  9. Ofer

    Ofer
    Expand Collapse
    Guest

    Is the subfieldname is a name of the field in your report?
    Is this field a text field and not a lable?
    The control source of this field should be empty
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Determined_Jim" wrote:

    > Ofer
    > There was a time when one could add a sub-total field to a report easily,
    > priot to PC languages. I chose this approach with concerns of end-users
    > forgetting to perform maintenance
    >
    > I coded as you described however I got an erro: You can't assign a value to
    > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > --
    > Jim
    >
    >
    > "Ofer" wrote:
    >
    > > I also can't see the logic behind what you are trying to do, but a way that
    > > you can achieve that
    > >
    > > 1. In the detail section create another text box under the record list, that
    > > will display the sub-total, set the property "Can shrink" of this text box
    > > and the Detail section to Yes
    > >
    > > 2. On the On format event of the detail section write the code
    > >
    > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > MyCount = MyCount + Me.Amount
    > > If Me.[acct#] = 201 Then
    > > Me.SumFieldname.Visible = True
    > > Me.SumFieldname= MyCount
    > > MyCount = 0
    > > Else
    > > Me.SumFieldname.Visible = False
    > > End If
    > >
    > > End Sub
    > > 3. Declair the MyAmount in the decleration of the report
    > >
    > > Option Compare Database
    > > Dim MyCount As Double
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "Determined_Jim" wrote:
    > >
    > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > sub-total after the detail line showing acct # 201l.
    > > >
    > > > Example
    > > > acct# Amount
    > > > 101 5.00
    > > > 201 3.00
    > > > sub 8.00
    > > > 301 6.00
    > > > 401 7.00
    > > >
    > > > How do I accomplish this. I have been to my manual but have found confusion
    > > >
    > > > Jim
     
  10. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "Determined_Jim" <DeterminedJim@discussions.microsoft.com> wrote in message
    news:693516D9-A1AB-4753-A62E-8F95523BDAC4@microsoft.com...

    > Albert
    > I missed a typo in my request with acct # 201|. It should have been 201.


    Ok..that helps. The problem here is that you still not asked a question in
    the context of how you plan to group the data

    > The acct #s can be grouped however I know how


    Ok, does mean you want to supply the list of accounts here?

    > to maintenance and chose to take on the responsibility of instituting
    > change
    > myself. I am from a technology prior to PC's in which one could add a
    > sub-total to a report rather easily.


    I too have worked on a ton of mainframe systems. Going back to Sperry
    mapper, Prime information, Microdata (MacDonald Douglas), Honeywell systems,
    IBM's "Universe" systems (U2), and of course Pick. And, in fact a good deal
    more systems then I can remember. I can't say any of those systems were even
    in the same league, and remotely close to the report writer that I got in
    ms-access. And, I used a ton of pc based systems from Advanced Revelation,
    FoxPro/dbaseII, Knowledge man, 4th dimension (mac), and again a ton more
    that I can't even remember...

    Creating sub-totals in ms-access is breeze, and even more incredible is that
    you don't even have to write any code....

    ms-access is BETTER then any of the systems I have EVER used....

    The problem here is not that some old mainframe system did this in a easier
    fashion (which I doubt by the way). The problem here is lack of definition
    of the problem.

    Do you want to "prompt" the user for the accounts grouping? So, each time,
    the user has to "enter" a bunch of accounts?

    Or, do you need/plan to pre-define the grouping in some "setup" form?

    Or, do you as the developer want to define this in the actual report (bad
    idea).

    It does not make sense to "hard code", or write some VB code to total
    particular accounts here. This approach means that you have to write code,
    or modify the report every you need a new grouping. This approach is just
    not workable, and one that is not very maintainable either..

    If you don't want to use my accounts+grouping idea, then another approach
    would be to supply a list of accounts,and use a sub-report, and that can
    generate the total for the accounts you choose.

    So, if you need to "prompt" the end users, then we do need to then setup a
    form, or some type of user interface that lets the user enter the accounts.
    Further, do you want restrict the report to those accounts you choose?

    I don't think you need to write code here (and, if you do, it would be
    outside of the report to setup the data). I would NOT try to code the
    groupings in the report unless you got no other choice, and have exhausted
    the above suggestions.

    There is a bunch of possibilities here, but you can't use concepts learned
    in the old dbaseIII language in ms-access, as they don't work the same. And,
    the approaches you used in one old system will not work the same in
    ms-access. You have to modify the approach you use to problem solving here,
    as the approaches used before change from platform to platform. I used to
    record numbers all the time in FoxPro, but now ms-access does NOT have
    record numbers. In fact, in ms-access data written to a file and then
    retrieved does NOT necessary come back in the same order! (hint: you have to
    "set" the order of data now). So, many concepts and ideas that used to make
    sense do not anymore.

    So, if you define the problem, then approaches can be suggested...not the
    other way around....

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  11. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Ofer
    I got around the error by clearing the control source field as you
    suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    working. The Sub-Total field (subfieldname) returned a value equal to the
    detail amount. I saved the script then changed it to list sub-totals for each
    detail entry. Every line is returning its detail amount value to the
    sub-total instead of adding to it. What am I missing?

    Jim


    "Ofer" wrote:

    > Is the subfieldname is a name of the field in your report?
    > Is this field a text field and not a lable?
    > The control source of this field should be empty
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "Determined_Jim" wrote:
    >
    > > Ofer
    > > There was a time when one could add a sub-total field to a report easily,
    > > priot to PC languages. I chose this approach with concerns of end-users
    > > forgetting to perform maintenance
    > >
    > > I coded as you described however I got an erro: You can't assign a value to
    > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > --
    > > Jim
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > you can achieve that
    > > >
    > > > 1. In the detail section create another text box under the record list, that
    > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > and the Detail section to Yes
    > > >
    > > > 2. On the On format event of the detail section write the code
    > > >
    > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > MyCount = MyCount + Me.Amount
    > > > If Me.[acct#] = 201 Then
    > > > Me.SumFieldname.Visible = True
    > > > Me.SumFieldname= MyCount
    > > > MyCount = 0
    > > > Else
    > > > Me.SumFieldname.Visible = False
    > > > End If
    > > >
    > > > End Sub
    > > > 3. Declair the MyAmount in the decleration of the report
    > > >
    > > > Option Compare Database
    > > > Dim MyCount As Double
    > > > --
    > > > If I answered your question, please mark it as an answer. That way, it will
    > > > stay saved for a longer time, so other can benifit from it.
    > > >
    > > > Good luck
    > > >
    > > >
    > > >
    > > > "Determined_Jim" wrote:
    > > >
    > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > sub-total after the detail line showing acct # 201l.
    > > > >
    > > > > Example
    > > > > acct# Amount
    > > > > 101 5.00
    > > > > 201 3.00
    > > > > sub 8.00
    > > > > 301 6.00
    > > > > 401 7.00
    > > > >
    > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > >
    > > > > Jim
     
  12. Ofer

    Ofer
    Expand Collapse
    Guest

    Can you post the full code under your report?
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Determined_Jim" wrote:

    > Ofer
    > I got around the error by clearing the control source field as you
    > suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    > working. The Sub-Total field (subfieldname) returned a value equal to the
    > detail amount. I saved the script then changed it to list sub-totals for each
    > detail entry. Every line is returning its detail amount value to the
    > sub-total instead of adding to it. What am I missing?
    >
    > Jim
    >
    >
    > "Ofer" wrote:
    >
    > > Is the subfieldname is a name of the field in your report?
    > > Is this field a text field and not a lable?
    > > The control source of this field should be empty
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "Determined_Jim" wrote:
    > >
    > > > Ofer
    > > > There was a time when one could add a sub-total field to a report easily,
    > > > priot to PC languages. I chose this approach with concerns of end-users
    > > > forgetting to perform maintenance
    > > >
    > > > I coded as you described however I got an erro: You can't assign a value to
    > > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > > --
    > > > Jim
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > > you can achieve that
    > > > >
    > > > > 1. In the detail section create another text box under the record list, that
    > > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > > and the Detail section to Yes
    > > > >
    > > > > 2. On the On format event of the detail section write the code
    > > > >
    > > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > > MyCount = MyCount + Me.Amount
    > > > > If Me.[acct#] = 201 Then
    > > > > Me.SumFieldname.Visible = True
    > > > > Me.SumFieldname= MyCount
    > > > > MyCount = 0
    > > > > Else
    > > > > Me.SumFieldname.Visible = False
    > > > > End If
    > > > >
    > > > > End Sub
    > > > > 3. Declair the MyAmount in the decleration of the report
    > > > >
    > > > > Option Compare Database
    > > > > Dim MyCount As Double
    > > > > --
    > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > stay saved for a longer time, so other can benifit from it.
    > > > >
    > > > > Good luck
    > > > >
    > > > >
    > > > >
    > > > > "Determined_Jim" wrote:
    > > > >
    > > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > > sub-total after the detail line showing acct # 201l.
    > > > > >
    > > > > > Example
    > > > > > acct# Amount
    > > > > > 101 5.00
    > > > > > 201 3.00
    > > > > > sub 8.00
    > > > > > 301 6.00
    > > > > > 401 7.00
    > > > > >
    > > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > > >
    > > > > > Jim
     
  13. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    a/c # Desc Amt
    101 Cash 800.00
    201 Equip 3000.00 3000.00

    Private Sub Description_Format (Cancel As Integer, Formatcount As Integer)
    MyCount = MyCount + Me.Amount
    If Me.[Acct No] = 201 Then
    Me.Sub_Total_amt.visible = true
    Me.Sub_Total_Amt = MyCount
    MyCount = 0
    Else
    Me.Sub_Total_amt.Visible False
    End If
    End Sub

    Jim

    --
    Another Learning Experience -- Jim


    "Ofer" wrote:

    > Can you post the full code under your report?
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "Determined_Jim" wrote:
    >
    > > Ofer
    > > I got around the error by clearing the control source field as you
    > > suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    > > working. The Sub-Total field (subfieldname) returned a value equal to the
    > > detail amount. I saved the script then changed it to list sub-totals for each
    > > detail entry. Every line is returning its detail amount value to the
    > > sub-total instead of adding to it. What am I missing?
    > >
    > > Jim
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Is the subfieldname is a name of the field in your report?
    > > > Is this field a text field and not a lable?
    > > > The control source of this field should be empty
    > > > --
    > > > If I answered your question, please mark it as an answer. That way, it will
    > > > stay saved for a longer time, so other can benifit from it.
    > > >
    > > > Good luck
    > > >
    > > >
    > > >
    > > > "Determined_Jim" wrote:
    > > >
    > > > > Ofer
    > > > > There was a time when one could add a sub-total field to a report easily,
    > > > > priot to PC languages. I chose this approach with concerns of end-users
    > > > > forgetting to perform maintenance
    > > > >
    > > > > I coded as you described however I got an erro: You can't assign a value to
    > > > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > > > --
    > > > > Jim
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > > > you can achieve that
    > > > > >
    > > > > > 1. In the detail section create another text box under the record list, that
    > > > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > > > and the Detail section to Yes
    > > > > >
    > > > > > 2. On the On format event of the detail section write the code
    > > > > >
    > > > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > > > MyCount = MyCount + Me.Amount
    > > > > > If Me.[acct#] = 201 Then
    > > > > > Me.SumFieldname.Visible = True
    > > > > > Me.SumFieldname= MyCount
    > > > > > MyCount = 0
    > > > > > Else
    > > > > > Me.SumFieldname.Visible = False
    > > > > > End If
    > > > > >
    > > > > > End Sub
    > > > > > 3. Declair the MyAmount in the decleration of the report
    > > > > >
    > > > > > Option Compare Database
    > > > > > Dim MyCount As Double
    > > > > > --
    > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > >
    > > > > > Good luck
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Determined_Jim" wrote:
    > > > > >
    > > > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > > > sub-total after the detail line showing acct # 201l.
    > > > > > >
    > > > > > > Example
    > > > > > > acct# Amount
    > > > > > > 101 5.00
    > > > > > > 201 3.00
    > > > > > > sub 8.00
    > > > > > > 301 6.00
    > > > > > > 401 7.00
    > > > > > >
    > > > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > > > >
    > > > > > > Jim
     
  14. Ofer

    Ofer
    Expand Collapse
    Guest

    Where did you declare the MyCount?

    Did you declare in under the report declaretion, as Double?

    Option Compare Database
    Dim MyCount As Double
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Determined_Jim" wrote:

    > a/c # Desc Amt
    > 101 Cash 800.00
    > 201 Equip 3000.00 3000.00
    >
    > Private Sub Description_Format (Cancel As Integer, Formatcount As Integer)
    > MyCount = MyCount + Me.Amount
    > If Me.[Acct No] = 201 Then
    > Me.Sub_Total_amt.visible = true
    > Me.Sub_Total_Amt = MyCount
    > MyCount = 0
    > Else
    > Me.Sub_Total_amt.Visible False
    > End If
    > End Sub
    >
    > Jim
    >
    > --
    > Another Learning Experience -- Jim
    >
    >
    > "Ofer" wrote:
    >
    > > Can you post the full code under your report?
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "Determined_Jim" wrote:
    > >
    > > > Ofer
    > > > I got around the error by clearing the control source field as you
    > > > suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    > > > working. The Sub-Total field (subfieldname) returned a value equal to the
    > > > detail amount. I saved the script then changed it to list sub-totals for each
    > > > detail entry. Every line is returning its detail amount value to the
    > > > sub-total instead of adding to it. What am I missing?
    > > >
    > > > Jim
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Is the subfieldname is a name of the field in your report?
    > > > > Is this field a text field and not a lable?
    > > > > The control source of this field should be empty
    > > > > --
    > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > stay saved for a longer time, so other can benifit from it.
    > > > >
    > > > > Good luck
    > > > >
    > > > >
    > > > >
    > > > > "Determined_Jim" wrote:
    > > > >
    > > > > > Ofer
    > > > > > There was a time when one could add a sub-total field to a report easily,
    > > > > > priot to PC languages. I chose this approach with concerns of end-users
    > > > > > forgetting to perform maintenance
    > > > > >
    > > > > > I coded as you described however I got an erro: You can't assign a value to
    > > > > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > > > > --
    > > > > > Jim
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > > > > you can achieve that
    > > > > > >
    > > > > > > 1. In the detail section create another text box under the record list, that
    > > > > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > > > > and the Detail section to Yes
    > > > > > >
    > > > > > > 2. On the On format event of the detail section write the code
    > > > > > >
    > > > > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > > > > MyCount = MyCount + Me.Amount
    > > > > > > If Me.[acct#] = 201 Then
    > > > > > > Me.SumFieldname.Visible = True
    > > > > > > Me.SumFieldname= MyCount
    > > > > > > MyCount = 0
    > > > > > > Else
    > > > > > > Me.SumFieldname.Visible = False
    > > > > > > End If
    > > > > > >
    > > > > > > End Sub
    > > > > > > 3. Declair the MyAmount in the decleration of the report
    > > > > > >
    > > > > > > Option Compare Database
    > > > > > > Dim MyCount As Double
    > > > > > > --
    > > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > > >
    > > > > > > Good luck
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > "Determined_Jim" wrote:
    > > > > > >
    > > > > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > > > > sub-total after the detail line showing acct # 201l.
    > > > > > > >
    > > > > > > > Example
    > > > > > > > acct# Amount
    > > > > > > > 101 5.00
    > > > > > > > 201 3.00
    > > > > > > > sub 8.00
    > > > > > > > 301 6.00
    > > > > > > > 401 7.00
    > > > > > > >
    > > > > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > > > > >
    > > > > > > > Jim
     
  15. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Ofer
    You Have been a great help. I finally have the sub-total field working when
    displayed on my monitor however when I print a copy of the report the
    sub-total field shows a zero. How do I fix this?

    Jim


    "Ofer" wrote:

    > Where did you declare the MyCount?
    >
    > Did you declare in under the report declaretion, as Double?
    >
    > Option Compare Database
    > Dim MyCount As Double
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "Determined_Jim" wrote:
    >
    > > a/c # Desc Amt
    > > 101 Cash 800.00
    > > 201 Equip 3000.00 3000.00
    > >
    > > Private Sub Description_Format (Cancel As Integer, Formatcount As Integer)
    > > MyCount = MyCount + Me.Amount
    > > If Me.[Acct No] = 201 Then
    > > Me.Sub_Total_amt.visible = true
    > > Me.Sub_Total_Amt = MyCount
    > > MyCount = 0
    > > Else
    > > Me.Sub_Total_amt.Visible False
    > > End If
    > > End Sub
    > >
    > > Jim
    > >
    > > --
    > > Another Learning Experience -- Jim
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > Can you post the full code under your report?
    > > > --
    > > > If I answered your question, please mark it as an answer. That way, it will
    > > > stay saved for a longer time, so other can benifit from it.
    > > >
    > > > Good luck
    > > >
    > > >
    > > >
    > > > "Determined_Jim" wrote:
    > > >
    > > > > Ofer
    > > > > I got around the error by clearing the control source field as you
    > > > > suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    > > > > working. The Sub-Total field (subfieldname) returned a value equal to the
    > > > > detail amount. I saved the script then changed it to list sub-totals for each
    > > > > detail entry. Every line is returning its detail amount value to the
    > > > > sub-total instead of adding to it. What am I missing?
    > > > >
    > > > > Jim
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > Is the subfieldname is a name of the field in your report?
    > > > > > Is this field a text field and not a lable?
    > > > > > The control source of this field should be empty
    > > > > > --
    > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > >
    > > > > > Good luck
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Determined_Jim" wrote:
    > > > > >
    > > > > > > Ofer
    > > > > > > There was a time when one could add a sub-total field to a report easily,
    > > > > > > priot to PC languages. I chose this approach with concerns of end-users
    > > > > > > forgetting to perform maintenance
    > > > > > >
    > > > > > > I coded as you described however I got an erro: You can't assign a value to
    > > > > > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > > > > > --
    > > > > > > Jim
    > > > > > >
    > > > > > >
    > > > > > > "Ofer" wrote:
    > > > > > >
    > > > > > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > > > > > you can achieve that
    > > > > > > >
    > > > > > > > 1. In the detail section create another text box under the record list, that
    > > > > > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > > > > > and the Detail section to Yes
    > > > > > > >
    > > > > > > > 2. On the On format event of the detail section write the code
    > > > > > > >
    > > > > > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > > > > > MyCount = MyCount + Me.Amount
    > > > > > > > If Me.[acct#] = 201 Then
    > > > > > > > Me.SumFieldname.Visible = True
    > > > > > > > Me.SumFieldname= MyCount
    > > > > > > > MyCount = 0
    > > > > > > > Else
    > > > > > > > Me.SumFieldname.Visible = False
    > > > > > > > End If
    > > > > > > >
    > > > > > > > End Sub
    > > > > > > > 3. Declair the MyAmount in the decleration of the report
    > > > > > > >
    > > > > > > > Option Compare Database
    > > > > > > > Dim MyCount As Double
    > > > > > > > --
    > > > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > > > >
    > > > > > > > Good luck
    > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > > > "Determined_Jim" wrote:
    > > > > > > >
    > > > > > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > > > > > sub-total after the detail line showing acct # 201l.
    > > > > > > > >
    > > > > > > > > Example
    > > > > > > > > acct# Amount
    > > > > > > > > 101 5.00
    > > > > > > > > 201 3.00
    > > > > > > > > sub 8.00
    > > > > > > > > 301 6.00
    > > > > > > > > 401 7.00
    > > > > > > > >
    > > > > > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > > > > > >
    > > > > > > > > Jim
     
  16. Determined_Jim

    Determined_Jim
    Expand Collapse
    Guest

    Ofer
    I removed the MyCount = 0 statement and my printed copy showed what my
    monitored displayed. Again I thank you for all of your help
    --
    Another Learning Experience -- Jim


    "Determined_Jim" wrote:

    > Ofer
    > You Have been a great help. I finally have the sub-total field working when
    > displayed on my monitor however when I print a copy of the report the
    > sub-total field shows a zero. How do I fix this?
    >
    > Jim
    >
    >
    > "Ofer" wrote:
    >
    > > Where did you declare the MyCount?
    > >
    > > Did you declare in under the report declaretion, as Double?
    > >
    > > Option Compare Database
    > > Dim MyCount As Double
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "Determined_Jim" wrote:
    > >
    > > > a/c # Desc Amt
    > > > 101 Cash 800.00
    > > > 201 Equip 3000.00 3000.00
    > > >
    > > > Private Sub Description_Format (Cancel As Integer, Formatcount As Integer)
    > > > MyCount = MyCount + Me.Amount
    > > > If Me.[Acct No] = 201 Then
    > > > Me.Sub_Total_amt.visible = true
    > > > Me.Sub_Total_Amt = MyCount
    > > > MyCount = 0
    > > > Else
    > > > Me.Sub_Total_amt.Visible False
    > > > End If
    > > > End Sub
    > > >
    > > > Jim
    > > >
    > > > --
    > > > Another Learning Experience -- Jim
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > Can you post the full code under your report?
    > > > > --
    > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > stay saved for a longer time, so other can benifit from it.
    > > > >
    > > > > Good luck
    > > > >
    > > > >
    > > > >
    > > > > "Determined_Jim" wrote:
    > > > >
    > > > > > Ofer
    > > > > > I got around the error by clearing the control source field as you
    > > > > > suggested. Now I am finding the Mycount = Mycount + Amount statement isn't
    > > > > > working. The Sub-Total field (subfieldname) returned a value equal to the
    > > > > > detail amount. I saved the script then changed it to list sub-totals for each
    > > > > > detail entry. Every line is returning its detail amount value to the
    > > > > > sub-total instead of adding to it. What am I missing?
    > > > > >
    > > > > > Jim
    > > > > >
    > > > > >
    > > > > > "Ofer" wrote:
    > > > > >
    > > > > > > Is the subfieldname is a name of the field in your report?
    > > > > > > Is this field a text field and not a lable?
    > > > > > > The control source of this field should be empty
    > > > > > > --
    > > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > > >
    > > > > > > Good luck
    > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > "Determined_Jim" wrote:
    > > > > > >
    > > > > > > > Ofer
    > > > > > > > There was a time when one could add a sub-total field to a report easily,
    > > > > > > > priot to PC languages. I chose this approach with concerns of end-users
    > > > > > > > forgetting to perform maintenance
    > > > > > > >
    > > > > > > > I coded as you described however I got an erro: You can't assign a value to
    > > > > > > > this object Me.subfieldname = Myaccount. Do you have any suggestions?
    > > > > > > > --
    > > > > > > > Jim
    > > > > > > >
    > > > > > > >
    > > > > > > > "Ofer" wrote:
    > > > > > > >
    > > > > > > > > I also can't see the logic behind what you are trying to do, but a way that
    > > > > > > > > you can achieve that
    > > > > > > > >
    > > > > > > > > 1. In the detail section create another text box under the record list, that
    > > > > > > > > will display the sub-total, set the property "Can shrink" of this text box
    > > > > > > > > and the Detail section to Yes
    > > > > > > > >
    > > > > > > > > 2. On the On format event of the detail section write the code
    > > > > > > > >
    > > > > > > > > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    > > > > > > > > MyCount = MyCount + Me.Amount
    > > > > > > > > If Me.[acct#] = 201 Then
    > > > > > > > > Me.SumFieldname.Visible = True
    > > > > > > > > Me.SumFieldname= MyCount
    > > > > > > > > MyCount = 0
    > > > > > > > > Else
    > > > > > > > > Me.SumFieldname.Visible = False
    > > > > > > > > End If
    > > > > > > > >
    > > > > > > > > End Sub
    > > > > > > > > 3. Declair the MyAmount in the decleration of the report
    > > > > > > > >
    > > > > > > > > Option Compare Database
    > > > > > > > > Dim MyCount As Double
    > > > > > > > > --
    > > > > > > > > If I answered your question, please mark it as an answer. That way, it will
    > > > > > > > > stay saved for a longer time, so other can benifit from it.
    > > > > > > > >
    > > > > > > > > Good luck
    > > > > > > > >
    > > > > > > > >
    > > > > > > > >
    > > > > > > > > "Determined_Jim" wrote:
    > > > > > > > >
    > > > > > > > > > I am attempting to produce a report with detail amounts. I need to add a
    > > > > > > > > > sub-total after the detail line showing acct # 201l.
    > > > > > > > > >
    > > > > > > > > > Example
    > > > > > > > > > acct# Amount
    > > > > > > > > > 101 5.00
    > > > > > > > > > 201 3.00
    > > > > > > > > > sub 8.00
    > > > > > > > > > 301 6.00
    > > > > > > > > > 401 7.00
    > > > > > > > > >
    > > > > > > > > > How do I accomplish this. I have been to my manual but have found confusion
    > > > > > > > > >
    > > > > > > > > > Jim
     

Share This Page