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?

CodePudding user response：

Change name of textbox so it is not same as field, like tbxRole. Then simple 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.