Chnage a value of a field in a query for a form only

[GLT]

Hi,

I am trying to make a change in value for a field in a query which is
displayed on a subform.

I have tried this:

Up: IIf([Up]="Fail","F")

I get a circular reference error - can anyone advise the correct way to do
this?

Any help is always greatly appreciated...

Cheers,
GLT.

 

[Allen Browne]

You've changed the Control Source of the text box.
You need to change its Name as well.

Access gets confused if the text box has the same name as a field, but is
bound to something different.

 

[John W. Vinson]

Hi,

I am trying to make a change in value for a field in a query which is
displayed on a subform.

I have tried this:

Up: IIf([Up]="Fail","F")

I get a circular reference error - can anyone advise the correct way to do
this?

Two problems with this: first off you are in fact creating a circular
reference - the field Up is being changed into a (different) field Up;
secondly, you aren't providing the False branch of the If (what do you want to
see if [Up] is NOT equal to "Fail"?)

I would suggest simply including Up in the subform's recordsource query, as
is, no IIf or other function; and put a Textbox on the subform with a control
source

= IIF([Up] = "Fail", "F", "whatever you want to see otherwise")

The "whatever" could just be blank, by putting "" as the third argument.

 

[John Spencer]

You can try fully qualifying the reference to the field by including the table
name in your calculated column (field). It seems to work for me. If you need
to apply criteria against this calculated field, I'm not so sure you won't get
an error. So try:
Up: IIF([YourTableName].[Up]="Fail","F",Null)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County