I thought this was going to be a simple thing, but... no so much. I could just leave it alone, but now I want the answer, and I cannot find an example of what I want, so I am going to ask if anyone understands what I am doing wrong (which is guaranteed).
I have a form, built directly from a table. In the field [users]![role], I wanted the result to remove an "_", to make it nicer on the eyes. So, I tried to go into the Properties of the form, to the Control Source & using the Expression Builder, say =iif([role]="End_User","End User",[role])
This seems logical to me. But this errors, saying it is a "circular reference" and "Invalid Control Property:Control Source".
I just want the UI to read "End User" and not "End_User". I have tried [users]![role]; [users]![role].[text]; taking off the "" from the result - I cannot make it work. Thoughts?
Change name of textbox so it is not same as field, like tbxRole. Or do this calculation in query and use query as form RecordSource.
Then could use expression: Replace([role], "_", " "). Won't matter what the data is - if there is an underscore it will be replaced with space, if there isn't then the data is unchanged.