subtracting a ending number on one line from the beginning number

[Tommy Boy]

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.

Thank You.

 

[Marshall Barton]

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.

Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Tommy Boy]

Marshall

Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.

Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Marshall Barton]

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])
--
Marsh
MVP [MS Access]

Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.

Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Tommy Boy]

Marshall

I Know were getting close, thanks for your good thoughts.

I made the changes and entered the new code and made the change to the
SetFlagR. I'm getting the following error message to this code:

Option Compare Database
Option Explicit
Public PageSum1 As Double
Public PageSum2 As Double
Public PageSum As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
PageSum = PageSum + Reports![Monthly Log]![Number of Voids]
PageSum1 = PageSum1 + Reports![Monthly Log]![Net Copies]
PageSum2 = PageSum2 + Reports![Monthly Log]![Net Copies] * 2
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' reset the counter for each new page

PageSum = 0
PageSum1 = 0
PageSum2 = 0
End Sub

Dim GlobalFlagR As Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlagR <> x - 1 Then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = x
End Function

The line "Private Sub PageHeader ...." is highlighted in yellow and the "'
reset the counter for each new page" is in green. Also the "Dim" (before
GlobalFlagR) is shaded in Blue

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])
--
Marsh
MVP [MS Access]

Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

Tommy Boy wrote:

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.


Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Marshall Barton]

Where did all that PageSum stuff come from? Whereever it
was, it won't work. As I tried to explain in another one of
your threads, you can not use event procedures to calculate
a value across multiple records - period!

All module level variables **must** be declared before the
first procedure. Move the Dim GlobalFlagR up near the top
of the module.

You also missed an important point about setting
GlobalFlagR, it needs to be:
GlobalFlagR = y

I can't locate where you call the SetFlagR function. Isn't
it supposed to be in the detail's Format event procedure?
--
Marsh
MVP [MS Access]

I Know were getting close, thanks for your good thoughts.

I made the changes and entered the new code and made the change to the
SetFlagR. I'm getting the following error message to this code:

Option Compare Database
Option Explicit
Public PageSum1 As Double
Public PageSum2 As Double
Public PageSum As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
PageSum = PageSum + Reports![Monthly Log]![Number of Voids]
PageSum1 = PageSum1 + Reports![Monthly Log]![Net Copies]
PageSum2 = PageSum2 + Reports![Monthly Log]![Net Copies] * 2
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' reset the counter for each new page

PageSum = 0
PageSum1 = 0
PageSum2 = 0
End Sub

Dim GlobalFlagR As Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlagR <> x - 1 Then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = x
End Function

The line "Private Sub PageHeader ...." is highlighted in yellow and the "'
reset the counter for each new page" is in green. Also the "Dim" (before
GlobalFlagR) is shaded in Blue

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])

Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

:

Tommy Boy wrote:

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.


Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Tommy Boy]

Marshall

The two changes (changing the x to a y) and moving the Dim statement to the
top did the trick. Thank you for your help, I've been trying to do this
little thing for years and always go to a frustration point and dropped it.
It now works great.

As to the Pade Sum stuff... I don't know exactly why it owrks but I'm able
to get page and reprot totals by using it. Somewhere along the line someone
helped me wit this code at its works fine ... go figure.

Again thank you, hope I didn't put you out too much.

Your the Bomb .... Tom

Where did all that PageSum stuff come from? Whereever it
was, it won't work. As I tried to explain in another one of
your threads, you can not use event procedures to calculate
a value across multiple records - period!

All module level variables **must** be declared before the
first procedure. Move the Dim GlobalFlagR up near the top
of the module.

You also missed an important point about setting
GlobalFlagR, it needs to be:
GlobalFlagR = y

I can't locate where you call the SetFlagR function. Isn't
it supposed to be in the detail's Format event procedure?
--
Marsh
MVP [MS Access]

I Know were getting close, thanks for your good thoughts.

I made the changes and entered the new code and made the change to the
SetFlagR. I'm getting the following error message to this code:

Option Compare Database
Option Explicit
Public PageSum1 As Double
Public PageSum2 As Double
Public PageSum As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
PageSum = PageSum + Reports![Monthly Log]![Number of Voids]
PageSum1 = PageSum1 + Reports![Monthly Log]![Net Copies]
PageSum2 = PageSum2 + Reports![Monthly Log]![Net Copies] * 2
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' reset the counter for each new page

PageSum = 0
PageSum1 = 0
PageSum2 = 0
End Sub

Dim GlobalFlagR As Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlagR <> x - 1 Then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = x
End Function

The line "Private Sub PageHeader ...." is highlighted in yellow and the "'
reset the counter for each new page" is in green. Also the "Dim" (before
GlobalFlagR) is shaded in Blue

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])


Tommy Boy wrote:
Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

:

Tommy Boy wrote:

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.


Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Marshall Barton]

Glad to hear the flag stuff is working.

However, the page and report total stuff might appear to
work for the moment, but there are a myriad of things in a
report that you might change that will cause that approach
to disintegrate. If you want page totals, use this
technique:
http://support.microsoft.com/kb/296249/en-us

If you want report and/or group totals, the first thing to
try is the Sum function. If you have a situation where you
are grouping and want to sum a group level value, then use a
text box with the RunningSum property. There are many other
situations where the RunningSum property can also be a life
saver so spend some time getting familiar with it.
--
Marsh
MVP [MS Access]

The two changes (changing the x to a y) and moving the Dim statement to the
top did the trick. Thank you for your help, I've been trying to do this
little thing for years and always go to a frustration point and dropped it.
It now works great.

As to the Pade Sum stuff... I don't know exactly why it owrks but I'm able
to get page and reprot totals by using it. Somewhere along the line someone
helped me wit this code at its works fine ... go figure.

Again thank you, hope I didn't put you out too much.

Your the Bomb .... Tom

Where did all that PageSum stuff come from? Whereever it
was, it won't work. As I tried to explain in another one of
your threads, you can not use event procedures to calculate
a value across multiple records - period!

All module level variables **must** be declared before the
first procedure. Move the Dim GlobalFlagR up near the top
of the module.

You also missed an important point about setting
GlobalFlagR, it needs to be:
GlobalFlagR = y

I can't locate where you call the SetFlagR function. Isn't
it supposed to be in the detail's Format event procedure?

I Know were getting close, thanks for your good thoughts.

I made the changes and entered the new code and made the change to the
SetFlagR. I'm getting the following error message to this code:

Option Compare Database
Option Explicit
Public PageSum1 As Double
Public PageSum2 As Double
Public PageSum As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
PageSum = PageSum + Reports![Monthly Log]![Number of Voids]
PageSum1 = PageSum1 + Reports![Monthly Log]![Net Copies]
PageSum2 = PageSum2 + Reports![Monthly Log]![Net Copies] * 2
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' reset the counter for each new page

PageSum = 0
PageSum1 = 0
PageSum2 = 0
End Sub

Dim GlobalFlagR As Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlagR <> x - 1 Then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = x
End Function

The line "Private Sub PageHeader ...." is highlighted in yellow and the "'
reset the counter for each new page" is in green. Also the "Dim" (before
GlobalFlagR) is shaded in Blue


:

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])


Tommy Boy wrote:
Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

:

Tommy Boy wrote:

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.


Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])

 

[Tommy Boy]

Thanks again, I'll try out the reference you gave me.

Tom

Glad to hear the flag stuff is working.

However, the page and report total stuff might appear to
work for the moment, but there are a myriad of things in a
report that you might change that will cause that approach
to disintegrate. If you want page totals, use this
technique:
http://support.microsoft.com/kb/296249/en-us

If you want report and/or group totals, the first thing to
try is the Sum function. If you have a situation where you
are grouping and want to sum a group level value, then use a
text box with the RunningSum property. There are many other
situations where the RunningSum property can also be a life
saver so spend some time getting familiar with it.
--
Marsh
MVP [MS Access]

The two changes (changing the x to a y) and moving the Dim statement to the
top did the trick. Thank you for your help, I've been trying to do this
little thing for years and always go to a frustration point and dropped it.
It now works great.

As to the Pade Sum stuff... I don't know exactly why it owrks but I'm able
to get page and reprot totals by using it. Somewhere along the line someone
helped me wit this code at its works fine ... go figure.

Again thank you, hope I didn't put you out too much.

Your the Bomb .... Tom

Where did all that PageSum stuff come from? Whereever it
was, it won't work. As I tried to explain in another one of
your threads, you can not use event procedures to calculate
a value across multiple records - period!

All module level variables **must** be declared before the
first procedure. Move the Dim GlobalFlagR up near the top
of the module.

You also missed an important point about setting
GlobalFlagR, it needs to be:
GlobalFlagR = y

I can't locate where you call the SetFlagR function. Isn't
it supposed to be in the detail's Format event procedure?


Tommy Boy wrote:
I Know were getting close, thanks for your good thoughts.

I made the changes and entered the new code and made the change to the
SetFlagR. I'm getting the following error message to this code:

Option Compare Database
Option Explicit
Public PageSum1 As Double
Public PageSum2 As Double
Public PageSum As Double

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
PageSum = PageSum + Reports![Monthly Log]![Number of Voids]
PageSum1 = PageSum1 + Reports![Monthly Log]![Net Copies]
PageSum2 = PageSum2 + Reports![Monthly Log]![Net Copies] * 2
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' reset the counter for each new page

PageSum = 0
PageSum1 = 0
PageSum2 = 0
End Sub

Dim GlobalFlagR As Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlagR <> x - 1 Then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = x
End Function

The line "Private Sub PageHeader ...." is highlighted in yellow and the "'
reset the counter for each new page" is in green. Also the "Dim" (before
GlobalFlagR) is shaded in Blue


:

I suggest you use a different name for this function so
there's no conflict at worst or confusion at best.

Dim GlobalFlagR as Variant

Function SetFlagR(x As Variant, y As Variant)
If GlobalFlag <> x - 1 then
SetFlagR = "*"
Else
SetFlagR = ""
End If

GlobalFlagR = y
End Function

Then call it using"
=SetFlagR([BegNo], [EndNo])


Tommy Boy wrote:
Thanks for the reponse. I understand the change to the =SetFlag...

But I don't understand how to change the code. I have for the other
situation:

Dim GlobalFlag as Variant

Function SetFlag(x as Variant)
If GlobalFlag <> x - 1 then
SetFlag = "*"
Else
SetFlag = ""
End If

GlobalFlag = x
End Function

I would appreicate your explanation of what to change?

Help Tom (ps, I'm pretty inexperinced in this stuff, but I'm getting things
done)

:

Tommy Boy wrote:

I'm running a report that has sequential ranges on a report. As an example
line one might have Beg No: 1 End No: 12, on the next line Beg No: 13 End
No: 15, third line Beg No: 17 End No: 24.

I have two issues: First I want to see if any numbers are missing between
sequences. The second is I'd like to flag the line if there is a gap.

I've just used the Flag out of sequence (210234) for another report,(it
worked great) but it doesn't cover when you have ranges.


Use the same technique. Just change it to compare
GlobalFlag to BegNo-1 and then set globalFlag to EndNo.

This means you need to change the function to accept two
arguments so the OutOfSequence text box expresssion can be:
=SetFlag([BegNo], [EndNo])