Picking out one item from a list

[Ken Mitchell]

Thanks to the excellent help that many of you here have provided, I've
managed to fool my boss into believing that I actually know something about
Access. But here's something I don't (yet) know how to do.

I'm working on an order-processing application. In the spirit of "Imitation
is the most sincere form of flattery", I have extensively plagi\\\\\adapted
the Northwinds database structure, so in the Order Details table, you will
find, among many others, a field called "PartNumber".

I am now writing a report for this order. If one of a list of part numbers
appear in the order, I'd like to show (or to make visible) a label or text
box to the OrderID footer. Writing the label or text box isn't the
challenge; even making it (in)visible is within my scope.

What bugs me is that I can't figure out how to write the if-then syntax to
look at the list of part numbers, see that one of them is there, and then
either display or hide the label.

So, if PartNumber AB123W appears in the OrderDetails for this order, I want
to display some information about the order. If this part does NOT appear in
the list, I want to hide this label or text box.

And of course, this is me; I'm looking for the easiest way to do it.

Any suggestions?

Thanks in advance for this, and thank you SO much for all you have helped me
with so far.

Ken Mitchell
Citrus Heights, CA
Where, in this time of "global warming", the temperature never exceeded 100
degrees in August, 2006!

 

[Al Campagna]

Ken,
Well, that text/comments associated with particular PartNos should really be displayed
in the same section of the report where the PNs display. (I'll assume the Detail
section - Order on the main, and Items in the Detail)
Lets say it involved something like a PN [SpecialReportDescription]. That field should
be in your PN table. Then you wouldn't have to code anything... the comments would flow
out along with it's associated PN.
In your scenario, what happens when another PN needs to have a comment associated with
it... you'd have to reprogram the footer for that instance, and etc etc in the future.
What happens when the comment text changes for a PN... the same coding "rework" would be
required.

What bugs me is that I can't figure out how to write the if-then syntax to
look at the list of part numbers, see that one of them is there, and then
either display or hide the label.

If you're saying several PNs on a report would need SpecialReportDescriptions, then all
the more reason to use the method above.

Please clarify if I've misunderstood. Even better, could you show a sample (what I'd
like to see) of the output...

 

[Duane Hookom]

You can use an expression in the control source of a text box like:

=IIf(Sum(PartNumber="AB123W") <>0,"Display this text",Null)

 

[Ken Mitchell]

Ken,
Well, that text/comments associated with particular PartNos should really be displayed
in the same section of the report where the PNs display. (I'll assume the Detail
section - Order on the main, and Items in the Detail)
Lets say it involved something like a PN [SpecialReportDescription]. That field should
be in your PN table. Then you wouldn't have to code anything... the comments would flow
out along with it's associated PN.

Hi, Al.

What I'm trying to do is to add some "boilerplate" at the end (or near the
end) of the report. But the text of the boilerplate will vary depending on
what items appear in the order.

Something like ....

------------------------------
Orde Header Stuff

Part Number Description Price
Ab123 This and That 12.06
SD456 Some Other Stuff 10.66
WE234 More stuff 17.76

Order Total Whatever

** This part includes a lot of other text that mostly nobody will care
about, but
which needs to be included in the Report Footer.

BY ordering Part WE234, you agree not to mix this with common household
detergents.

------------------------

So, if any Part in the WE* series is included in this order, I want to add
some boilerplate text in the report footer. And there's a different paragraph
I want to add it any part in the QB* series is ordered.

Clear as mud, right?

Thanks for your help!

 

[Ken Mitchell]

I had a long, rambling reply prepared that said, "close, but..." because I
needed something more elaborate and formatted. Then I sat back and thought
for a bit, and darn if this wasn't close enough! Thanks!

Would you mind explaining HOW it works? I don't exactly follow WHY the
construction of "sum(some string inequality)" is not equal to a numeric zero?

 

[Duane Hookom]

The expression
PartNumber="AB123W"
evaluates to either true/-1 or false/0.

If you sum these -1s or 0s, you will get either 0 if none of the expressions
are true or some negative number.

The IIf() just tests to see if the result of the Sum( -1s or 0s ) isn't 0.

 

[Ken Mitchell]

Thanks. That explains it perfectly, and I'm a bit sheepish that I didn't
figure that out for myself. I appreciate the assistance.