Printing a Report

[Guest]

How do I set a report to print everything, except for the null values? Can
anybody help me out with this? Printing fields in the report that are
actually filled in? How do I set it to filter out the null fields when I
print? I'm racking my brains. Thanks!

 

[Guest]

If you want to remove the all record that has a null in it, set the criteria
in the record source of the report

Select * from TableName Where FieldName Is Not Null

=======================
To remove only the field that has null in it, set the Can Shrink property of
the field and the section where the field is located in to Yes

 

[Guest]

Where at in the database do I find the TableName where FieldName is Not Null??

 

[Guest]

1. Open the report in design view
2. Open the report properties (Alt+Enter or View>properties)
3. One of the properties id "Record Source" put the cursor in it, on the
right you'll see a button with three dots, click on it
4. Now, in this SQL add a criteria to the desire field
Is Not Null
5. Close the SQL, you'll get a message if you want to save it, click Yes

I hope it clear, Good Luck

 

[Guest]

When I add "is not null" into the criteria, it deletes everything from that
company. I don't want it to delete the entire company, I simply just want it
to delete for example, the address section, if there is no address, not the
entire group of information for that company. Am I doing something wrong?

 

[Guest]

Try this again. It deletes all of my information in that specific group if I
put the "is not null" into the criteria. I simply just want it to delete the
criteria within the group that is null when it prints out, to free up space.
Does this make sense?

 

[Steve Schapel]

Jennifer,

I'm pretty sure I understand what you are talking about. In design view
of your report, try setting the Can Shrink property of the textboxes in
question to Yes, and also the Can Shrink property of the report section
(presumably the Detail section).

 

[Guest]

Where do I find the "Can Shrink" property for the textboxes and report
section at?

 

[Guest]

Steve,
That didn't work.

Jennifer,

I'm pretty sure I understand what you are talking about. In design view
of your report, try setting the Can Shrink property of the textboxes in
question to Yes, and also the Can Shrink property of the report section
(presumably the Detail section).

 

[Steve Schapel]

Jennifer,

Well, looks like I didn't understand after all. If you are trying to
reduce blank spaces on the report where there is no data in a field, the
Can Shrink would work, as lond as there are no other controls on the
report at the same horizontal level.

So, can you please give some details, with examples, of exactly what you
want to happen, and what is happening now that is different from what
you want? Thanks.

 

[Guest]

Steve,
I think I've figured out where my problem lies. I've got two separate
boxes, one that is the actual boxes that generates the information for the
field and one that just names the field. The generated box can be shrunk,
however, the box with just the name of the field cannot. Any tips? Thanks!

 

[Steve Schapel]

Jennifer,

It sounds like you have a textbox, which is bound to the field in the
report's underlying query, so this is where the actual data appears.
And then you have a label, on the same horizontal level on the report as
the textbox. Is this correct?

Ok, the Can Shrink property does not apply to a label.

One way that you can handle this is to change the label to a textbox.
This will be an unbound textbox. You can format it so it looks like the
label. And then, using your example of the Address field, you can put
something like this in this unbound textbox's Control Source property...
=IIf(IsNull([Address]),Null,"Address")
And then set this textbox's Can Shrink property also to Yes.
What will happen here is that if there is an address in the Address
field, the unbound textbox will display the word Address, and look like
a label, but if there is no address, it will not show anything, and
should Shrink along with the actual Address textbox. Hope that makes sense.

 

[Guest]

Steve,
I'm getting a box that pops up titled "Enter Parameter Value". What's this?

Jennifer,

It sounds like you have a textbox, which is bound to the field in the
report's underlying query, so this is where the actual data appears.
And then you have a label, on the same horizontal level on the report as
the textbox. Is this correct?

Ok, the Can Shrink property does not apply to a label.

One way that you can handle this is to change the label to a textbox.
This will be an unbound textbox. You can format it so it looks like the
label. And then, using your example of the Address field, you can put
something like this in this unbound textbox's Control Source property...
=IIf(IsNull([Address]),Null,"Address")
And then set this textbox's Can Shrink property also to Yes.
What will happen here is that if there is an address in the Address
field, the unbound textbox will display the word Address, and look like
a label, but if there is no address, it will not show anything, and
should Shrink along with the actual Address textbox. Hope that makes sense.

--
Steve Schapel, Microsoft Access MVP

Steve,
I think I've figured out where my problem lies. I've got two separate
boxes, one that is the actual boxes that generates the information for the
field and one that just names the field. The generated box can be shrunk,
however, the box with just the name of the field cannot. Any tips? Thanks!

 

[Steve Schapel]

Jennifer,

What is the parameter it is asking for? This would normally indicate
that you have a field referenced somehow in the report, that does not
exist. Spelling error, or changing a field name, are probably the two
most common causes.

 

[Guest]

One last question. It works, but when it shrinks, it's not deleting the
space in between, and there's 2 seperate text boxes in some (i.e. ofc ac &
ofc # that go with the office label). WHen I change the label to a text box
though, it doesn't keep the label name, it changes it to what I have in the
if statement. ANy suggestions?

 

[Steve Schapel]

Jennifer,

Doo you mean something like this...
=IIf(IsNull([ofc ac]+[ofc #]),Null,IIf(IsNull([ofc ac]),"ofc
#",IIf(IsNull([ofc #]),"ofc ac","ofc ac #")))

Otherwise, you will have to explain in a bit more detail exactly what
you want to happen. Thanks.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.

 

[Guest]

Yes, but the label that I was trying to turn into a text box, it changes the
wording to read what the "if" statement says...i.e for the ofc ac, it changes
the label from office to ofc ac

Jennifer,

Doo you mean something like this...
=IIf(IsNull([ofc ac]+[ofc #]),Null,IIf(IsNull([ofc ac]),"ofc
#",IIf(IsNull([ofc #]),"ofc ac","ofc ac #")))

Otherwise, you will have to explain in a bit more detail exactly what
you want to happen. Thanks.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.

--
Steve Schapel, Microsoft Access MVP

One last question. It works, but when it shrinks, it's not deleting the
space in between, and there's 2 seperate text boxes in some (i.e. ofc ac &
ofc # that go with the office label). WHen I change the label to a text box
though, it doesn't keep the label name, it changes it to what I have in the
if statement. ANy suggestions?

 

[Steve Schapel]

Jennifer,

Please tell me *exactly* what you have entered for the Control Source of
the label you have changed into a textbox. Also, please tell me
*exactly* what you want it to say. Also please tell me *exactly* what
is the name of the field that this "label" refers to. Also please tell
me *exactly* what conditions apply to the display of the "label". Thank
you.

--
Steve Schapel, Microsoft Access MVP

Yes, but the label that I was trying to turn into a text box, it changes the
wording to read what the "if" statement says...i.e for the ofc ac, it changes
the label from office to ofc ac

Jennifer,

Doo you mean something like this...
=IIf(IsNull([ofc ac]+[ofc #]),Null,IIf(IsNull([ofc ac]),"ofc
#",IIf(IsNull([ofc #]),"ofc ac","ofc ac #")))

Otherwise, you will have to explain in a bit more detail exactly what
you want to happen. Thanks.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.

--
Steve Schapel, Microsoft Access MVP

One last question. It works, but when it shrinks, it's not deleting the
space in between, and there's 2 seperate text boxes in some (i.e. ofc ac &
ofc # that go with the office label). WHen I change the label to a text box
though, it doesn't keep the label name, it changes it to what I have in the
if statement. ANy suggestions?

 

[Guest]

Steve,
Ok, I got everything to work, now I have a lot of white space. How can I
get rid of the white space in places that need it?

Jennifer,

Please tell me *exactly* what you have entered for the Control Source of
the label you have changed into a textbox. Also, please tell me
*exactly* what you want it to say. Also please tell me *exactly* what
is the name of the field that this "label" refers to. Also please tell
me *exactly* what conditions apply to the display of the "label". Thank
you.

--
Steve Schapel, Microsoft Access MVP

Yes, but the label that I was trying to turn into a text box, it changes the
wording to read what the "if" statement says...i.e for the ofc ac, it changes
the label from office to ofc ac

Jennifer,

Doo you mean something like this...
=IIf(IsNull([ofc ac]+[ofc #]),Null,IIf(IsNull([ofc ac]),"ofc
#",IIf(IsNull([ofc #]),"ofc ac","ofc ac #")))

Otherwise, you will have to explain in a bit more detail exactly what
you want to happen. Thanks.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.

--
Steve Schapel, Microsoft Access MVP


Jennifer wrote:
One last question. It works, but when it shrinks, it's not deleting the
space in between, and there's 2 seperate text boxes in some (i.e. ofc ac &
ofc # that go with the office label). WHen I change the label to a text box
though, it doesn't keep the label name, it changes it to what I have in the
if statement. ANy suggestions?

 

[Steve Schapel]

Jennifer,

Set the Can Shrink property to Yes for all textboxes which may be empty,
and all of the unbound textboxes that you are using to replace the
labels. You also need to set the Can Shrink property to Yes for the
applicable Section (presumably the Detail section) of the report.