[X]My Assistant

[wbhburn]

I have a very bulky option group in a form that has six option buttons. While the group displays correctly in my report, it takes up too much space. I assumed I could just do a nested iif in an unbound control to return the desired text (rather than all the text and buttons for all the options) but I continually get #type!. To test it I'm only using one condition in the control source ... =iif([Top] = 1, "Std") with Top being the name of the option group in the table/query. I know the data is there and 1 is a valid option because I placed the raw control [Top] on the report and it's displaying properly.

Are there some rules/syntax regarding option groups on reports and iifs that I'm not aware of? Any other suggestions?

[theDBguy]

Hi,

Welcome to UtterAccess!

Option Groups are good on forms for selecting choices, but they may not be necessary on reports? Can you replace the option groups on the report with the actual fields from your table based on the selected options?

Just curious...

[wbhburn]

FYI, I build replica windows for historic properties. Many windows have special top sash. I have the most common top sash options as options in an option group [Top] on the form I use to input the specific window data. This is just one of at least 15 fields in the window record.

Standard = 1
Arch = 2
Round = 3
Gothic = 4
Segmented = 5
Other = 6

The report in question is just a summary of each window in a project and there are many other pieces of data I want to see on the same line and I want the lines as close together as possible. Displaying the whole group take up too much space.

I can indeed just place the field [Top] on the form and it returns the correct number. However, I would then have to have the numbers memorized for the info to be valuable and I may not be the only one reading it. I'm not sure what you are suggesting but I still don't know why I can't do a nested iif based on the [Top] number to return the correct text in an unbound control.

Cheers!

[theDBguy]

Hi,

You should be able to use an IIf() statement, but it's limited to seven nested Ifs, so if you have a lot of options, you might run of room anyway. Can you post some screenshots of your form and report, so we can get some idea on how to help you? Thanks.

[wbhburn]

Thanks,

As you can see from the report, the field [Top] returns properly when it's merely another field in the report record but when used in the function it pukes.

AACapture1 is the report
AACapture2 is the report in design view
AACapture3 is the table
AACapture4 are the nested input forms I use to record the project details (Client-Project-Constants-Window)

Again, I'll next the iif when we get this first part figured out.

Cheers!

Attached File(s)

 AACapture1.JPG ( 42.33K )Number of downloads: 4
 AACapture2.JPG ( 72.72K )Number of downloads: 3
 AACapture3.JPG ( 87.3K )Number of downloads: 4
 AACapture4.JPG ( 128.09K )Number of downloads: 6

 

[theDBguy]

Hi,

On the form, is Top the one for the option group with Arch, Goth, Other, etc?

In the table, do you have anything in the Lookup tab for Top?

On the IIf() function, try using the name of the Textbox rather than [Top]. If the textbox is also named Top, try renaming it to something else. This is just to see if you get a different result or not.

[wbhburn]

Hi,

Top is the name of the option group on the form. It is also the name of the field in the table and query behind the report.

The lookup just says "display control" - Textbox

I'll test as you wish in a minute.

[wbhburn]

Changing the name of [Top] to [txtTop] and conditioning that did the trick. But why?

[theDBguy]

Hi,

I suspect partly because "Top" is a *reserved* word in Access. Try renaming your field to something else and then use the new name of the field in the IIf() function just to see if behaves differently.

Cheers!

[wbhburn]

FYI,

I got everything conditioned properly and it's returning the correct text. There are five iff's nested inside the original iif in the control!

Thanks for all the help but I'd still like to know why I had the problem in the first place?

[wbhburn]

Of course that's the problem!!!!

In the back of my mind I wondered if it was a reserve word I just never use but I guess I was too lazy to go check.

Thanks for all your effort.

For kicks I might try renaming the field in the table and then seeing if it will take the direct name in the report.

[theDBguy]

Hi,

You're welcome! Glad to hear you got it sorted out. Good luck with your project.