Conditional reporting

[Takashi]

I'm trying to draw a report of a table that has many checkboxes (yes/no). Each checkbox corresponds to text statement (stored in another table). I want to design a report that only prints the text statements corresponding to checkboxes with a value of 'yes'. I've tried using =If([ErrorCode1]=True,"Agreements"," ") and similar permutations on the report, but they leave ugly gaps.

Is there a way to make a query for only fields with a specified value?

The DB this is being done on was developed by someone else, but now I've inherited it. I was hoping to avoid too much de-construction/re-construction if at all possible.

Thanks

 

[Atomic Shrimp]

You could combine the text values into one field with something like:

=(Iif([ErrorCode1]=True,"Agreements "," ") & Iif([ErrorCode2]=True,"Something else "," ") & Iif([ErrorCode3]=True,"Again! "," "))

and so on

Mike

PS. You'll probably end up redeveloping this inherited database from scratch, mark my words!

[This message has been edited by Mike Gurman (edited 01-19-2001).]

 

[Takashi]

Thanks for the reply.

Somehow I figured it would come to that... I'm dreading a scratchbuild simply because the DB is not only large, but *live*.
(Only partially funcitonal, but live nonetheless)

 

[Chris RR]

Have you tried setting "Can Shrink" on for these fields? That might close up the gap without a lot of nasty code. (You may also need to set "Can Shrink" for the section that you're in...)

 

[Takashi]

OK, splitting each entry and using the 'can shrink' value closes the unsightly gaps. I have one more question though. I have all 60 error messages already in a table [Messages].

Is there a way to lookup the value in the table from the report expression? Something like:

=IIf([Forms]![Invoice]![Agreement4]=True,[Messages]![Message].Message_ID=4,"none")*

Where you pass a parameter in the expression, retrieving the message corresponding to message_id 4, as opposed to typing:

=IIf([Forms]![Invoice]![Agreement4]=True,"Credit Slip Not An Acceptable Receipt","none")

The message data already exists, and it would be a waste of time to 'hard code' the messages, lest they change in the future.

In case you couldn't figure it out, I'm my company's ad-hoc DB guy, since the old programmer walked out and we can't find a replacement. I'm completely new to access development and I greatly appreciate the help.


*(Don't try this code, it doesn't do anything)

 

[Takashi]

Nevermind

I fixed the problem with a short VB routine.