Running Sum

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I use Running Sum (Field Subtotal) in a report.

This is what report looks like:

Subtotal Running Sum
======== ===========
10 10
15 25
5 30
12 42

Here's what I'm trying to achieve...

If the value of "Running Sum" >= 30, I want to underline the FIRST RECORD
where the epxression is true.

I use an UNBOUND field with the following expression. The background color
is transparent, so I actually move
the UNBOUND FIELD across both fields (Subtotal and Running Sum)... it gives
me the underlining effect.

=IIF([RunningSum]>=30,"_______","")

The IIF works fine, except that it underlines ALL records where the
expression is true. So, in the example data
above, (5 30) & (12 42) is underlined (well, because both "30" and "42"
meet the criteria).

Does anyone know of a workaround solution (or even "nicer" solution than
mine) where ONLY THE 1ST RECORD MEETING THE
CRITERIA is underlined?


Thanks,
Tom
 
I would use code in the report like:

Option Compare Database
Public booHit As Boolean
Const intHitValue As Integer = 30

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.RunningSum >= intHitValue _
And booHit = False Then
'underline the running sum text box
Me.Line (Me.RunningSum.Left, _
Me.RunningSum.Top + Me.RunningSum.Height)-Step _
(Me.RunningSum.Width, 0)
booHit = True
End If
End Sub
 
Duane:

I haven't been able to get this to work yet...

while "fiddling" w/ this, I am wondering if my last postd thread provided
all info.

There may be times when the Running Sum does not equal exactly "30". For
instance, if the record list
15 15
5 20
11 31
6 37

then, I want the 3rd record listed (31>30 is true). But I don't want to
underline 37. Essentially,
I just want to create a breakline between "Funded" and "Unfunded" resources.

Tom




Duane Hookom said:
I would use code in the report like:

Option Compare Database
Public booHit As Boolean
Const intHitValue As Integer = 30

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.RunningSum >= intHitValue _
And booHit = False Then
'underline the running sum text box
Me.Line (Me.RunningSum.Left, _
Me.RunningSum.Top + Me.RunningSum.Height)-Step _
(Me.RunningSum.Width, 0)
booHit = True
End If
End Sub

--
Duane Hookom
MS Access MVP
--


Tom said:
I use Running Sum (Field Subtotal) in a report.

This is what report looks like:

Subtotal Running Sum
======== ===========
10 10
15 25
5 30
12 42

Here's what I'm trying to achieve...

If the value of "Running Sum" >= 30, I want to underline the FIRST RECORD
where the epxression is true.

I use an UNBOUND field with the following expression. The background color
is transparent, so I actually move
the UNBOUND FIELD across both fields (Subtotal and Running Sum)... it gives
me the underlining effect.

=IIF([RunningSum]>=30,"_______","")

The IIF works fine, except that it underlines ALL records where the
expression is true. So, in the example data
above, (5 30) & (12 42) is underlined (well, because both "30" and "42"
meet the criteria).

Does anyone know of a workaround solution (or even "nicer" solution than
mine) where ONLY THE 1ST RECORD MEETING THE
CRITERIA is underlined?


Thanks,
Tom
Click to expand...
Click to expand...
 
What are "Funded" and "Unfunded" resources? What isn't working? Do you have
some symptoms?
--
Duane Hookom
MS Access MVP


Tom said:
Duane:

I haven't been able to get this to work yet...

while "fiddling" w/ this, I am wondering if my last postd thread provided
all info.

There may be times when the Running Sum does not equal exactly "30". For
instance, if the record list
15 15
5 20
11 31
6 37

then, I want the 3rd record listed (31>30 is true). But I don't want to
underline 37. Essentially,
I just want to create a breakline between "Funded" and "Unfunded" resources.

Tom




Duane Hookom said:
I would use code in the report like:

Option Compare Database
Public booHit As Boolean
Const intHitValue As Integer = 30

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.RunningSum >= intHitValue _
And booHit = False Then
'underline the running sum text box
Me.Line (Me.RunningSum.Left, _
Me.RunningSum.Top + Me.RunningSum.Height)-Step _
(Me.RunningSum.Width, 0)
booHit = True
End If
End Sub

--
Duane Hookom
MS Access MVP
--


Tom said:
I use Running Sum (Field Subtotal) in a report.

This is what report looks like:

Subtotal Running Sum
======== ===========
10 10
15 25
5 30
12 42

Here's what I'm trying to achieve...

If the value of "Running Sum" >= 30, I want to underline the FIRST RECORD
where the epxression is true.

I use an UNBOUND field with the following expression. The background color
is transparent, so I actually move
the UNBOUND FIELD across both fields (Subtotal and Running Sum)... it gives
me the underlining effect.

=IIF([RunningSum]>=30,"_______","")

The IIF works fine, except that it underlines ALL records where the
expression is true. So, in the example data
above, (5 30) & (12 42) is underlined (well, because both "30" and "42"
meet the criteria).

Does anyone know of a workaround solution (or even "nicer" solution than
mine) where ONLY THE 1ST RECORD MEETING THE
CRITERIA is underlined?


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
Duane:

Thanks for the prompt reply...

right now, the entire function isn't working for me... I'm sure that I AM
doing something wrong.

When I opened the report, I simply clicked on the VBA icon and pasted the
code in the VBA window. Then I (certainly) updated the code to reflect my
table name and field structure.

I also updated the table records so that I would "hit" at least a record
with the value of "30". No record was underlined though. Again, not sure
what the problem is right now.

In respect to "funded" and "unfunded" resources... I'm simply trying to
track all of those projects that are within budgeted resources. For
instance, if I have $100,000
available, I want to know which projects (records) are below the $100k
(cumulative running sum) and which ones (again based, on cumulative) would
be outside the $100k mark. The records (projects) are sorted by
priorities.

Thanks again,
Tom





Duane Hookom said:
What are "Funded" and "Unfunded" resources? What isn't working? Do you have
some symptoms?
--
Duane Hookom
MS Access MVP


Tom said:
Duane:

I haven't been able to get this to work yet...

while "fiddling" w/ this, I am wondering if my last postd thread provided
all info.

There may be times when the Running Sum does not equal exactly "30". For
instance, if the record list
15 15
5 20
11 31
6 37

then, I want the 3rd record listed (31>30 is true). But I don't want to
underline 37. Essentially,
I just want to create a breakline between "Funded" and "Unfunded" resources.

Tom




Duane Hookom said:
I would use code in the report like:

Option Compare Database
Public booHit As Boolean
Const intHitValue As Integer = 30

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.RunningSum >= intHitValue _
And booHit = False Then
'underline the running sum text box
Me.Line (Me.RunningSum.Left, _
Me.RunningSum.Top + Me.RunningSum.Height)-Step _
(Me.RunningSum.Width, 0)
booHit = True
End If
End Sub

--
Duane Hookom
MS Access MVP
--


I use Running Sum (Field Subtotal) in a report.

This is what report looks like:

Subtotal Running Sum
======== ===========
10 10
15 25
5 30
12 42

Here's what I'm trying to achieve...

If the value of "Running Sum" >= 30, I want to underline the FIRST RECORD
where the epxression is true.

I use an UNBOUND field with the following expression. The background
color
is transparent, so I actually move
the UNBOUND FIELD across both fields (Subtotal and Running Sum)... it
gives
me the underlining effect.

=IIF([RunningSum]>=30,"_______","")

The IIF works fine, except that it underlines ALL records where the
expression is true. So, in the example data
above, (5 30) & (12 42) is underlined (well, because both "30" and
"42"
meet the criteria).

Does anyone know of a workaround solution (or even "nicer" solution than
mine) where ONLY THE 1ST RECORD MEETING THE
CRITERIA is underlined?


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
Have you tried placing a Break Point or MsgBox in the code? This will tell
you if the code is running and what some of the values might be.

--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks for the prompt reply...

right now, the entire function isn't working for me... I'm sure that I AM
doing something wrong.

When I opened the report, I simply clicked on the VBA icon and pasted the
code in the VBA window. Then I (certainly) updated the code to reflect my
table name and field structure.

I also updated the table records so that I would "hit" at least a record
with the value of "30". No record was underlined though. Again, not sure
what the problem is right now.

In respect to "funded" and "unfunded" resources... I'm simply trying to
track all of those projects that are within budgeted resources. For
instance, if I have $100,000
available, I want to know which projects (records) are below the $100k
(cumulative running sum) and which ones (again based, on cumulative) would
be outside the $100k mark. The records (projects) are sorted by
priorities.

Thanks again,
Tom





Duane Hookom said:
What are "Funded" and "Unfunded" resources? What isn't working? Do you have
some symptoms?
--
Duane Hookom
MS Access MVP


Tom said:
Duane:

I haven't been able to get this to work yet...

while "fiddling" w/ this, I am wondering if my last postd thread provided
all info.

There may be times when the Running Sum does not equal exactly "30". For
instance, if the record list
15 15
5 20
11 31
6 37

then, I want the 3rd record listed (31>30 is true). But I don't want to
underline 37. Essentially,
I just want to create a breakline between "Funded" and "Unfunded" resources.

Tom




I would use code in the report like:

Option Compare Database
Public booHit As Boolean
Const intHitValue As Integer = 30

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.RunningSum >= intHitValue _
And booHit = False Then
'underline the running sum text box
Me.Line (Me.RunningSum.Left, _
Me.RunningSum.Top + Me.RunningSum.Height)-Step _
(Me.RunningSum.Width, 0)
booHit = True
End If
End Sub

--
Duane Hookom
MS Access MVP
--


I use Running Sum (Field Subtotal) in a report.

This is what report looks like:

Subtotal Running Sum
======== ===========
10 10
15 25
5 30
12 42

Here's what I'm trying to achieve...

If the value of "Running Sum" >= 30, I want to underline the FIRST
RECORD
where the epxression is true.

I use an UNBOUND field with the following expression. The background
color
is transparent, so I actually move
the UNBOUND FIELD across both fields (Subtotal and Running Sum)... it
gives
me the underlining effect.

=IIF([RunningSum]>=30,"_______","")

The IIF works fine, except that it underlines ALL records where the
expression is true. So, in the example data
above, (5 30) & (12 42) is underlined (well, because both "30" and
"42"
meet the criteria).

Does anyone know of a workaround solution (or even "nicer"
Click to expand...
solution
than
mine) where ONLY THE 1ST RECORD MEETING THE
CRITERIA is underlined?


Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
Back
Top