hide controls

[rong]

I'm having a hard time hiding a control in a report.
I have a customer table with name and various inventories for each
customer like this:
name commodity grade weight rockwt
sandwt
Bob Smith rock #1 25000
John Doe rock #2 22300
John Doe rock & sand #1 20000 15000
5000

My report will print like the above until I get to the rock & sand. At
that point I would like to print out the rockwt and the sandwt only and
not the weight based on the fact that the values are not null or
greater than zero. Otherwise I don't want that control visible on the
report for all customers since only a few might have the rock & sand
mixture. My query has for each customer name, commodity, weight,
rockwt, sandwt. I've searched thru this group and found some examples
of how to hide or make visible controls but whenever I think I have the
right code I get the formatting page press control&break message.

Any help would be appreciated.

Thanks,
rong

 

[Steve Schapel]

Rong,

I think I know what you mean. If so, I would do it like this... In the
query, replace the Weight field by a calculated field like this...
WeightOnly: IIf(IsNull([rockwt]+[sandwt]),[weight],Null)
.... and then put this on the report in the place of the existing weight
control.

Does that do what you want?

 

[rkg]

I got the query to work thanks. Now I need to stop the label of the
textbox from printing on the report. Someone said to use the on format
on the group header but I haven't been able to get that one to work.
Any ideas?
Thanks,
rong

 

[rkg]

I mean I don't want the labels to print when the fields are empty.
Thanks again,
rong

 

[Steve Schapel]

Rong,

Replace the labels with unbound textboxes, change formating if necessary
to make it look like the other labels, and set the Control Source
property to the equivalent of...
=IIf(IsNull([YourDataField]),Null,"Your label text")

 

[Larry Linson]

I mean I don't want the labels to print when the fields are empty.
Thanks again,

As it appears the labels are printed in the Group Header, before you know
whether they are needed, that will probably require a change in approach. If
you use Steve's method to calculate a Weight, perhaps the "Sand and Rock"
line could look something like:

John Doe Rock & Sand #1 20000 15000(Rock) 5000(Sand)

or even better, always use a separate line for each commodity -- in which
case, you will not have a problem.

Larry Linson
Microsoft Access MVP

 

[rkg]

Worked perfectly, thanks. I have one other question. When doing my
report I need to match a weight to a price on another table that is not
in my query. I've tried to build the expression in my control source
property like we did above.
=IIf(IsNull([weight]),Null,([weight]*[table!price]) but it doesn't seem
to be able to find the price.There is only one record in the table.
I really appreciate your help.

rong

 

[Steve Schapel]

Rong,

Why not just add the table with the price to the query that the report
is based on? Then you could simply add a calculated field in the query,
by typing this into the Field row or a blank column in the query design
grid...
ExtPrice: [weight]*[price]
.... and then you can put a control on the report bound to this ExtPrice
field.

An alternative, less efficient but more in keeping with your existing
approach, would be this in the Control Source of the textbox on the
report...
=[weight]*DLookup("[price]","table")

Note that in neither of these examples is it necessary to test [weight]
for Null. If [weight] is null, then the calculation will also
automatically be null, there is no need to specify this.