Running Sum

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
 
D

Duane Hookom

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
 
T

Tom

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...
 
D

Duane Hookom

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...
 
Top