basic report help

[campanellisj]

How can I have a reports text field say a different remark based on a selection in a form?



For instance if the user selects option 1 from the main form the report says "option one selected", If he selects option 2 say "option 2 selected" etc etc...

There are 5 options in total but I need the remarks in those options to show on the report based on user selection. I tried the IIF statement but that didn't work. Is this doable? The options are in tblOption and the dropdown is in tblMain

[pbaldy]

Nested IIf() functions would work, as would the Switch() function, but what you should do is include the two tables in a query with a join on the option number field. That would enable you to bring in the descriptive value from the options table. Joining the tables is more efficient and more flexible (like when you change options).

[campanellisj]

Thanks for the fast response, I tried the nested approach and will look into the other two as soon as I post this. my issue again is that the options are actual paragraphs of text that are different depending on the option selected.
I attahced the DB to this, it's really basic only the two tables and the report. appreciate your help!

[pbaldy]

I expected to see the exception id or code in the main table. How do you determine which exception to put on the report without that?

[campanellisj]

I cleared it out cause I couldn't get it to work, I know thier should be an FK key there I must have just forgotten it. please add it in again.

[pbaldy]

So I have to do your work for you?

SELECT tblMainAttrsImport.Course, tblMainAttrsImport.Class, tblMainAttrsImport.SSN, tblMainAttrsImport.Name, tblException.Exception
FROM tblMainAttrsImport INNER JOIN tblException ON tblMainAttrsImport.ExceptionID = tblException.ExceptionID;

[campanellisj]

I'm sorry to be a bother, it's just a lil thing I was playing with nothing "work" related just an internal tool. I appreciate your help I really do.

[pbaldy]

No bother, I was just kidding around. Did that SQL solve the problem?

[campanellisj]

No I guess I can't do what I am thinking or I don't understand. I need the report to look at the installation field, see "polk" then display the polk exception (or it sees bragg and displays a different exception memo)

the users select the installation in a form but I wanted to create the report in such a way that the system saw the installation selected and displayed the standard memo field for that installation.

the exceptions themselves are quite lengthy but each one is different per installation. So the switch() doesn't work and the nestedIIF won't work cause of the length of all the different memo fields (not to mention the amount of installations avalible lol)

[pbaldy]

But when you say "see 'polk'", how is it supposed to see it? You would typically have a field that the two tables share, which is why I said I expected to see ExceptionID in the main table. Your original question was about how to display the text associated with the choice, but you aren't storing the choice, so how would you even do it with an IIf()? If you want to base it on the Installation field, then that field should be the key field in the exceptions table (or at least be a field in the table, so they can be joined).