iif() formula returning #Error in report

[Funkyaccess]

Hi

In one of my fields in my report I have the the control source set as follows:

=IIf(Forms!frm_standing_order_report!Option16.OptionValue=1,[bank_statement_date],[data_for])

Its returns #Error. I have an optiongroup that has radio buttons this is placed on a tab within one of my forms. If the value is not set I want it to default to [data_for]

 

[pbaldy]

It looks like you're testing one of the options. You test the value of the frame, which will have the value of the selected option.

 

[Funkyaccess]

Option16 is one on the radio buttons what do I use to obtain the optiongroups value?
My option groups name is optGroupView

 

[pbaldy]

Test the frame (option group):

=IIf(Forms!frm_standing_order_report.optGroupView=1,[bank_statement_date],[data_for])

 

[Funkyaccess]

Hi Paul,

Thanks for your help. Unfortunately its still returning #Error in my report
Is there another way to set the controlsource other than using IIF().

 

[pbaldy]

Is that form open when the report is open? It needs to be. The option group isn't on a subform, is it?

 

[boblarson]

Sorry for nosing in, but is it possible that either of these two fields:

[bank_statement_date]
[data_for]

have nulls?

 

[pbaldy]

I don't believe Nulls would matter in this situation.

 

[Funkyaccess]

Paul - I have a tab control setup on my form and the option group has been placed on one tab.

Bob - No they dont have nulls

 

[pbaldy]

And the form is open while the report is? Can you post a sample of the db?

 

[Funkyaccess]

Paul the form is open when the report is run. unfortunately I cannot post the db as it does contain sensitive data, but I'll try and create a dummy db and see if it works or post it here.

 

[boblarson]

Paul the form is open when the report is run. unfortunately I cannot post the db as it does contain sensitive data, but I'll try and create a dummy db and see if it works or post it here.

If you make a copy of the database for Paul, you can QUICKLY scramble/obscure the data by using this tool from MVP Roger Carlson (modified by me using code by MVP Mark Davis, from Utter Access). (see attached).

 

[Funkyaccess]

Hi bob,

Thanks I'll keep that sample in case I need it in the future.

I've created a really simple demo. Basically open form1, go to tab 2 then choose a radio button, click "run report" the report should either show the id or the name field from tbltest. Instead you get #Error

 

[pbaldy]

You can get around the error by renaming the control from ID to txtID (and watch out for Access changing the formula to match, which you don't want).

Then you'll run into the second problem.

 

[Funkyaccess]

so its name conflict. why when I select name does it return the name of the report. Is that because "Name" is a reserved word?

 

[pbaldy]

Yes it is. I'd change the field name, if it's not too late. The kludge workaround would be to put a hidden textbox in the report named "txtName" and refer to that.

 

[Funkyaccess]

Thanks no the name conflict (i.e Name column) is only in the demo. I did have a control that had a Name of data_for and another with bank_statement_date. I renamed both and it works perfectly.

Thanks yet again. Rep points on their way.

 

[pbaldy]

No problem, glad we got it sorted out.