Suppressing info in specific records on the report

[Paul Fenton]

We have a tee-shirt orders table consisting of the following fields:

Name
Small
Medium
Large
XL
2XL
3XL
etc.

For each name, we'll usually have just one shirt ordered in one of
the sizes. I'm trying to print mailing labels and can come up with
this.

John Smith

SM MD LG 1
XL 2XL 3XL

Mary Jones

SM MD LG
XL 1 2XL 3XL

What I want to see is this:

John Smith
LG 1

Mary Jones

XL 1

Is there a way to suppress those labels printing when the value for
that particular field is 0 or null? I was trying something like

If isnull(me!txtSM) or me!txtSM = 0 then
me!lblSMl.visible = False
End If

But I can't figure out where in the report to put it. Detail section?
Print? Paint? Format?

Thanks for any help

(e-mail address removed)

 

[Duane Hookom]

I would print the labels from a normalized table. You can normalize with a
union query like:
SELECT [Name], "SM" as Size, [Small] As Quantity
FROM tblUnnormalized
WHERE Small Is Not Null
UNION ALL
SELECT [Name], "MD", [Medium]
FROM tblUnnormalized
WHERE Medium Is Not Null
UNION ALL
SELECT [Name], "LG", [Large]
FROM tblUnnormalized
WHERE Large Is Not Null
UNION ALL
-- etc --
;
You can then create labels based on the union query.

 

[Paul Fenton]

Thank you Duane. That will work.