Line Spacing?

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a report that has a field for questions and five
fields for mutiple choice answers. Sometimes there are
only three mutiple choice answers so two fields are
blank. (would always be last ones blank). My question is
when a field is blank how can I have the report move
other lines up. Here's a sample of what it looks like and
what I want it to look like.
Thanks Rick

1. What is a Nickle?
5 cents
10 cents
25 cents
(here's my problem this line would be blank)
(this line also would be blank if no answer listed)
2. This would be next question.

so again I would like to move question 2. up two lines if
there are no answers, but if there are answers it would
stay where it is.
Thanks again,
Rick
 
Rick:

To in essence skip outputting null records you use a property of the report
called .PrintSection.

In the On Print event of the Detail Section, add VBA Code like this:

If IsNull(Me!NameOfAnswerField) or Len(Me!NameOfAnswerField)<1 Then
Me.PrintSection = False
Me.NextRecord = True
End If

HTH
 
Steve, or anyone else,
I am new to this and don't know VBA is it possible to do
another way?
Thanks Rick
 
Okay I tried the VBA code and here's what I put in and I
get a compile error (invalid outside procedure) and the
ME is highlighted.

Option Compare Database

If Len(Me!AnswerE) < 1 Then
Me.PrintSection = False
Me.NextRecord = True
End If

Sub RemoveE()

End Sub

Any help?
Thanks Rick
 
Rick:

As noted in my original post, where this code needs to go is in the On Print
event of the Detail section. You had put it in the general section of the
report's module which is why you got a compile error.

1.) Delete that code.
2.) Understand what the On Print event is: When an Access report is run,
each time it hits a specific section various events occur, the section is
formatted, it prints (some times it retreats to a previous section and
re-formats). etc. So what you are doing by putting VBA code in the On
Print event is that you want that code to run each time the Print event of
the detail section of the report occurs.
3.) To add this code, open the report in design mode; select the detail
section's divider bar and bring up the properties window. At the bottom,
you'll see a list of events such as On Format or On Print. Click on that
event an you should see a little combo box drop down on the right appear,
click than and in the list that appears, you'll see first something called
an "Event Procedure" (followed by a list of any macros you have in the
database.) Select event procedure. Once that is done, then you should see
a new little button appear to the right with an elipsis (...) click there
and it will open the On Print Event for the Detail section. Add the code
discussed there.

HTH
 
I was going post a new thread but as we are on the subject
i thought i would just hijack this one (Hope you dont
mind).

i too want to be able to skip out blank lines but can't
believe this is the only way to do it.

i looked through a few pages on this newsgroup to see if i
could find other posts related to the same thing. someone
suggested using the 'can shrink' property but this didnt
work for me and the blank lines are still there.

just a thought.....is there and expression that you can
use to put one field below another in a text box. for
example....if i wanted to concatenate 2 fields i would do
this : [field1]&" "&[field2]. is there something you can
typ into that that makes field 2 go below field 1?
 
Steve,
I found the problem, I also have a text box next to the
AnswerE. In that text box is the following code =IIf
([Answer E],"E."," ") that way if nothing in the record
for answer E nothing will show in the text box, but if
something in record for AnswerE it will put a E. in the
text box. If I remove the text box the code you gave me
works. Is there something I can add to be able to leave
the text box work and move the lines up? Again my report
would print like this:
1. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
E. Answer E (the E. is the text box)
2. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
(remove this line text box and Answer E)
Again THANK YOU FOR YOUR TIME AND HELP
Rick
 
Steve, Also the problem that I had with the code was my
field is named Answer E, but when I put it into your code
had to put it like this Answer_E, found that out by left
click on mouse and it brings up a list of things that can
be entered. Again thanks for your patient. Rick
-----Original Message-----
Steve,
I found the problem, I also have a text box next to the
AnswerE. In that text box is the following code =IIf
([Answer E],"E."," ") that way if nothing in the record
for answer E nothing will show in the text box, but if
something in record for AnswerE it will put a E. in the
text box. If I remove the text box the code you gave me
works. Is there something I can add to be able to leave
the text box work and move the lines up? Again my report
would print like this:
1. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
E. Answer E (the E. is the text box)
2. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
(remove this line text box and Answer E)
Again THANK YOU FOR YOUR TIME AND HELP
Rick
-----Original Message-----
Rick:

As noted in my original post, where this code needs to go is in the On Print
event of the Detail section. You had put it in the general section of the
report's module which is why you got a compile error.

1.) Delete that code.
2.) Understand what the On Print event is: When an Access report is run,
each time it hits a specific section various events occur, the section is
formatted, it prints (some times it retreats to a previous section and
re-formats). etc. So what you are doing by putting
Click to expand...
VBA
code in the On
Print event is that you want that code to run each time the Print event of
the detail section of the report occurs.
3.) To add this code, open the report in design mode; select the detail
section's divider bar and bring up the properties window. At the bottom,
you'll see a list of events such as On Format or On Print. Click on that
event an you should see a little combo box drop down on the right appear,
click than and in the list that appears, you'll see first something called
an "Event Procedure" (followed by a list of any macros you have in the
database.) Select event procedure. Once that is done, then you should see
a new little button appear to the right with an elipsis (...) click there
and it will open the On Print Event for the Detail section. Add the code
discussed there.

HTH
Click to expand...
and
I
Click to expand...
to
do
Click to expand...
there
are


.
Click to expand...
.
Click to expand...
 
I found the problem, I also have a text box next to the
AnswerE. In that text box is the following code =IIf
([Answer E],"E."," ") that way if nothing in the record
for answer E nothing will show in the text box, but if
something in record for AnswerE it will put a E. in the
text box. If I remove the text box the code you gave me
works. Is there something I can add to be able to leave
the text box work and move the lines up? Again my report
would print like this:
1. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
E. Answer E (the E. is the text box)
2. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
(remove this line text box and Answer E)
Click to expand...


Be careful here, the expression you posted:

=IIf([Answer E],"E."," ")

returns a space character in the answer letter text box.

This is neither a zero length string (known as a ZLS and
written in code as "") or a Null, so CanShrink can't squeeze
up the section's unused "line" because the line is used to
display the single space character.

If [Answer E] contains a ZLS or Null, then the Answer text
box will be able to shrink.

If [Answer E] is a ZLS, then the answer letter text box
could be made shrinkable by using:
=IIf([Answer E] = "", Null, "E.")
or, if [Answer E] is Null, you can use:
=IIf(IsNull([Answer E]), Null, "E.")
or, if [Answer E] could be either a ZLS or Null, then you
can check for either situation this way:
=IIf(Nz([Answer E], "") = "", Null, "E.")

Now, when all the controls in a "line" can shrink, then the
section can shrink to squeeze out the completely unused
line, which should take care of the problem.
 
Marsh,
Thank you, the first one didn't work it placed the E.
every record(even if blank)
=IIf([Answer E] = "", Null, "E.")
the second one worked
=IIf(IsNull([Answer E]), Null, "E.")
Thank you very much, Thank everyone this is such a great
board, so much help here.
Thanks again,
Rick


-----Original Message-----
I found the problem, I also have a text box next to the
AnswerE. In that text box is the following code =IIf
([Answer E],"E."," ") that way if nothing in the record
for answer E nothing will show in the text box, but if
something in record for AnswerE it will put a E. in the
text box. If I remove the text box the code you gave me
works. Is there something I can add to be able to leave
the text box work and move the lines up? Again my report
would print like this:
1. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
E. Answer E (the E. is the text box)
2. Question
A. Answer A
B. Answer B
C. Answer C
D. Answer D
(remove this line text box and Answer E)
Click to expand...


Be careful here, the expression you posted:

=IIf([Answer E],"E."," ")

returns a space character in the answer letter text box.

This is neither a zero length string (known as a ZLS and
written in code as "") or a Null, so CanShrink can't squeeze
up the section's unused "line" because the line is used to
display the single space character.

If [Answer E] contains a ZLS or Null, then the Answer text
box will be able to shrink.

If [Answer E] is a ZLS, then the answer letter text box
could be made shrinkable by using:
=IIf([Answer E] = "", Null, "E.")
or, if [Answer E] is Null, you can use:
=IIf(IsNull([Answer E]), Null, "E.")
or, if [Answer E] could be either a ZLS or Null, then you
can check for either situation this way:
=IIf(Nz([Answer E], "") = "", Null, "E.")

Now, when all the controls in a "line" can shrink, then the
section can shrink to squeeze out the completely unused
line, which should take care of the problem.
Click to expand...
 
Back
Top