Adding a line based on values

[Guest]

I have a report sorted by week ending date. It looks something like this.

1/02/2005 1 5 10 74
1/09/2005 2 6 12 74
1/16/2005 3 8 16 136
1/23/2005 4 9 18 136

What I want is a line to go horizontally all the way accross the report
(from the first column to the last) when the value in the last column changes
from 74 to 136 in between 74 and 136. Is this going to be possible. Any
help is appreciated.

 

[Rick Brandt]

I have a report sorted by week ending date. It looks something like
this.

1/02/2005 1 5 10 74
1/09/2005 2 6 12 74
1/16/2005 3 8 16 136
1/23/2005 4 9 18 136

What I want is a line to go horizontally all the way accross the
report (from the first column to the last) when the value in the last
column changes from 74 to 136 in between 74 and 136. Is this going
to be possible. Any help is appreciated.

Add a Group Section based on the fifth field and place a line in that
section.

 

[Guest]

When I add a group section and put a line it gives me a line after every row.
I just want a line when the value in column 5 changes from 74 to 136. So I
can see the change. Thank you for the reply. Any other ideas?

 

[Rick Brandt]

When I add a group section and put a line it gives me a line after
every row. I just want a line when the value in column 5 changes from
74 to 136. So I can see the change. Thank you for the reply. Any
other ideas?

You have to group on a field in the report's recordsource or an expression that
uses one or more of those fields. In your case you would add a group footer
based on the field that contains the 74 and 136 values.

You leave your existing controls in the detail section and put the line into the
group footer section. With that setup the group footer will only be printed on
each change of value in that field.

 

[Guest]

I really apreciate your help. But I am doing exactly what you describe and
it is putting a line under every row. What am I doing wrong?

 

[Guest]

I should also mention that I already have two other grouping levels. I have
MonthEnd and WeekEnd. I actually got it to print the line in between the 74
and 136 and not after every row, but it still prints another line after every
MonthEnd. I just want it between 74 and 136, no where else. Thanks again.

 

[Rick Brandt]

I should also mention that I already have two other grouping levels.
I have MonthEnd and WeekEnd. I actually got it to print the line in
between the 74 and 136 and not after every row, but it still prints
another line after every MonthEnd. I just want it between 74 and
136, no where else. Thanks again.

Well if the other group levels are "outside" the group for the line then
you will get a line when they change as well.

You could eliminate the group section idea and place the line in the Detail
section under your textboxes and then have code in your OnFormat or OnPrint
event of that section.

(Place this in the top of the report's code module)
Dim OldVal as Long

(Place this in the section event)
If Me!FieldName = OldVal Then
Me!LineName.Visible = False
Else
Me!LineName.Visible = True
End if
OldVal = Me!FieldName

That should cause the line to appear only when the value changes.

 

[Guest]

Thank you so much for your help. Here is what I have:

Option Compare Database
Dim OldVal As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!Card = OldVal Then
Me!Line84.Visible = False
Else
Me!Line84.Visible = True
End If
OldVal = Me!Card
End Sub

It is giving me a runtime error 13 - Type Mismatch and is pointing to OldVal
= Me!Card at the end. I tried to move Dim OldVal as Long and it doesn't give
me an error, but it still puts the line after every row. This is my last
obstacle I have from completing this dang report. I really appreciate your
help.

 

[Rick Brandt]

Thank you so much for your help. Here is what I have:

Option Compare Database
Dim OldVal As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!Card = OldVal Then
Me!Line84.Visible = False
Else
Me!Line84.Visible = True
End If
OldVal = Me!Card
End Sub

It is giving me a runtime error 13 - Type Mismatch and is pointing to
OldVal = Me!Card at the end. I tried to move Dim OldVal as Long and
it doesn't give me an error, but it still puts the line after every
row. This is my last obstacle I have from completing this dang
report. I really appreciate your help.

I was guessing, but what is the datatype of the field [Card]? The error
suggests that it is not a long integer. Does it ever contain fractions? Is
it actually Text? You need to change...

Dim OldVal as Long

....to whatever DataType is appropriate for the field [Card] (or else use
Variant).

 

[Guest]

Yeah, card is actually a text box. You have to forgive me, I am not very
keen when it comes to code. So now I have:

Option Compare Database
Dim OldVal As TextBox
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!Card = OldVal Then
Me!Line84.Visible = False
Else
Me!Line84.Visible = True
End If
OldVal = Me!Card
End Sub

And it gives me another error:
Run-time error '91':
Object variable or With block variable not set

I just want it to work.

Thank you so much for your help. Here is what I have:

Option Compare Database
Dim OldVal As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!Card = OldVal Then
Me!Line84.Visible = False
Else
Me!Line84.Visible = True
End If
OldVal = Me!Card
End Sub

It is giving me a runtime error 13 - Type Mismatch and is pointing to
OldVal = Me!Card at the end. I tried to move Dim OldVal as Long and
it doesn't give me an error, but it still puts the line after every
row. This is my last obstacle I have from completing this dang
report. I really appreciate your help.

I was guessing, but what is the datatype of the field [Card]? The error
suggests that it is not a long integer. Does it ever contain fractions? Is
it actually Text? You need to change...

Dim OldVal as Long

....to whatever DataType is appropriate for the field [Card] (or else use
Variant).

 

[Rick Brandt]

Yeah, card is actually a text box. You have to forgive me, I am not
very keen when it comes to code. So now I have:

Dim OldVal As TextBox

No. We are not examining the control. We are examining the field that is being
displayed in the control. That will have a DataType of Text, Number, Currency,
etc..

For Text you would use a String variable.

 

[Guest]

OK I got it. The last problem now is if one of the records is blank it is
giving me an error saying "invalid use of Null." Is there something I need
to include in the code to ignore null values or something. Again I really do
appreciate your help tons.

 

[Rick Brandt]

OK I got it. The last problem now is if one of the records is blank
it is giving me an error saying "invalid use of Null." Is there
something I need to include in the code to ignore null values or
something. Again I really do appreciate your help tons.

Either Dim as type Variant or use...

OldVal = Nz(Me!Card, "")

 

[Guest]

You are being so helpful. This works fine except then it puts a line after
every record with a null value. I need no line when there is no value. I
promise this is my last question. Thank you so much.