Tags: access, afield, based, cost, database, detail, financial, goal, goods, indent, indenting, line, microsoft, mysql, oracle, report, report1, sql, upon, value

Indenting a detail line

On Database » Microsoft Access

7,984 words with 7 Comments; publish: Thu, 05 Jun 2008 09:17:00 GMT; (250125.00, « »)

Is there a way to indent a detail line in a report based upon the value of a

field in that detail? My goal is like a financial report:

1 Cost of Goods Sold

2 Hardware

3 Bolts $100.00

3 Nuts $150.00

2 Total Hardware $250.00

1 Total Cost of Goods $250.00

Thanks for any help.

All Comments

Leave a comment...

  • 7 Comments
    • "DavidES" <dseibold .ms-access.todaysummary.com.wildhorses4x4. com.(donotspam)> wrote:

      >Is there a way to indent a detail line in a report based upon the value of a

      >field in that detail? My goal is like a financial report:

      >1 Cost of Goods Sold

      > 2 Hardware

      > 3 Bolts $100.00

      > 3 Nuts $150.00

      > 2 Total Hardware $250.00

      >1 Total Cost of Goods $250.00

      Well, yes, there is a way to "indent", but your example is

      (or should be) using group header/footer sections. Each

      section is positioned as needed with no need to adjust them.

      Based on the example, I don't understand what you want to do

      with the details or how a field in a record has anything to

      do with it.

      An example of a few records in the reports record source

      might help explain how you want to transform the query's

      records into the report's output.

      Marsh

      MVP [MS Access]

      #1; Thu, 05 Jun 2008 09:18:00 GMT
    • Thanks Marshall, I will try to explain my situation. My table lists the

      following using my sample data:

      ListNum Name Category Level Amount

      10 Cost of Goods Sold Title 1

      20 Hardware Title 2

      30 Bolts 3 100.00

      40 Nuts 3 150.00

      50 Total Hardware Total 2 250.00

      60 Total Cost of Goods Total 1 250.00

      The ListNum is the order in which I wish the line to be listed.

      The Level determines the indent I want for the line.

      I've pretty much given up on the indent and I'm working with a series of

      text controls positioned where I want them with conditional properties based

      upon the level value... and it works great except that I can't find a way to

      make the unused controls invisible which results in the control on top

      whiting out the control under it where they overlap.

      I'm looking into using the OnFormat event to create a case statement to make

      the unused controls invisible. Maybe that is my solution.

      #2; Thu, 05 Jun 2008 09:19:00 GMT
    • "DavidES" <dseibold .ms-access.todaysummary.com.wildhorses4x4. com.(donotspam)> wrote:

      >Thanks Marshall, I will try to explain my situation. My table lists the

      >following using my sample data:

      >ListNum Name Category Level Amount

      > 10 Cost of Goods Sold Title 1

      > 20 Hardware Title 2

      > 30 Bolts 3 100.00

      > 40 Nuts 3 150.00

      > 50 Total Hardware Total 2 250.00

      > 60 Total Cost of Goods Total 1 250.00

      >The ListNum is the order in which I wish the line to be listed.

      >The Level determines the indent I want for the line.

      >I've pretty much given up on the indent and I'm working with a series of

      >text controls positioned where I want them with conditional properties based

      >upon the level value... and it works great except that I can't find a way to

      >make the unused controls invisible which results in the control on top

      >whiting out the control under it where they overlap.

      >I'm looking into using the OnFormat event to create a case statement to make

      >the unused controls invisible. Maybe that is my solution.

      That is one wierd table. Looks more like a spreadsheet than

      a database table.

      You can do it by "indenting" or by making things visible.

      personally, I would do the indenting:

      Sub Detail_Format(

      Dim indent As Long

      indent = (.5 * 1440) * (Me.txtLevel -1)'1/2 inch/level

      Me.txtListNum.Left = indent

      Me.txtName.Left = indent

      Me.txtListNum.Left = indent

      Me.txtAmount.Left = indent

      End Sub

      Marsh

      MVP [MS Access]

      #3; Thu, 05 Jun 2008 09:20:00 GMT
    • I'm willing to try your suggestion... where do I put it? and it seems to be

      missing something in the first line...

      Thanks for your help...

      and yes, it is a weird table made from a download of a spreadsheet but the

      best I could figure to do.

      "Marshall Barton" wrote:

      > Sub Detail_Format(

      > Dim indent As Long

      > indent = (.5 * 1440) * (Me.txtLevel -1)'1/2 inch/level

      > Me.txtListNum.Left = indent

      > Me.txtName.Left = indent

      > Me.txtListNum.Left = indent

      > Me.txtAmount.Left = indent

      > End Sub

      > --

      > Marsh

      > MVP [MS Access]

      >

      #4; Thu, 05 Jun 2008 09:21:00 GMT
    • The first line is just a place holder that tells you where

      to put the rest of the code ;-)

      In other words, the code goes in the detail section's Format

      event procedure. I assumed that the same place where you

      were playing around with the visible stuff.

      Marsh

      MVP [MS Access]

      "DavidES" <dseibold .ms-access.todaysummary.com.wildhorses4x4. com.(donotspam)> wrote:[vbcol=seagreen]

      >I'm willing to try your suggestion... where do I put it? and it seems to be

      >missing something in the first line...

      >

      >and yes, it is a weird table made from a download of a spreadsheet but the

      >best I could figure to do.

      >"Marshall Barton" wrote:

      #5; Thu, 05 Jun 2008 09:22:00 GMT
    • Yes! that was the problem... we were writing on top of ourselves... The

      txtAmount control was being written on top of the txtName control and in my

      situation, the txtAmount was null so it looked like half of the name was cut

      off.

      Indent measures from the left margin for each use so I had to add inches to

      the txtAmount indent to put it where I wanted it.

      Here's the working code.

      Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

      Dim indent As Long

      indent = (0.25 * 1440) * (Me.txtLevel - 1) '1/4 inch/level

      Me.txtName.Left = indent

      Me.txtAmount.Left = indent + 4320 '4320 is 3 inch indent

      End Sub

      This is much easier than the code I was trying using the case method. Thanks

      for showing it to me!

      "Marshall Barton" wrote:

      > It sounds like some other controls are on top of the ones

      > we're indenting. To check fo this, you can set all of the

      > controls in the details section to have a different back

      > color.

      > If that doen't clarify what's happening, please post more

      > information including a Copy/Paste of the code in the event

      > procedure.

      > --

      > Marsh

      > MVP [MS Access]

      >

      > "DavidES" <dseibold .ms-access.todaysummary.com.wildhorses4x4. com.(donotspam)> wrote:

      >

      #6; Thu, 05 Jun 2008 09:23:00 GMT
    • Good catch David.

      My code made the (dumb?) assumption that the text boxes were

      one above the other. Your adjustment is the kind of thing

      that's needed when they are side by side.

      You might find it a little more flexible if you used:

      Me.txtAmount.Left = Me.txtName.Left + Me.txtName.Width

      instead, just in case you should ever change the size of the

      txtName text box.

      Marsh

      MVP [MS Access]

      "DavidES" <dseibold .ms-access.todaysummary.com.wildhorses4x4. com.(donotspam)> wrote:[vbcol=seagreen]

      >Yes! that was the problem... we were writing on top of ourselves... The

      >txtAmount control was being written on top of the txtName control and in my

      >situation, the txtAmount was null so it looked like half of the name was cut

      >off.

      >Indent measures from the left margin for each use so I had to add inches to

      >the txtAmount indent to put it where I wanted it.

      >Here's the working code.

      >Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

      >Dim indent As Long

      > indent = (0.25 * 1440) * (Me.txtLevel - 1) '1/4 inch/level

      > Me.txtName.Left = indent

      > Me.txtAmount.Left = indent + 4320 '4320 is 3 inch indent

      >End Sub

      >This is much easier than the code I was trying using the case method. Thanks

      >for showing it to me!

      >

      >"Marshall Barton" wrote:

      #7; Thu, 05 Jun 2008 09:24:00 GMT