Reverse the sign of an access report bound control

[Guest]

I am printing a report from a table which includes the fields Account Type (which can be 1 or 2) and Actual Period (which can be positive or negative). If the account type is 2 I want to print Actual Period with the opposite sign.

I have created a calculated field in the detail section with a control source as follows :

iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

When I run the report I get #Error in the calculated field.

[Actual Period] and [Account Type] are both bound controls in the detail section, although they are both invisible.

Can anyone think what I could be doing wrong ?

 

[Allen Browne]

Change the Name of the control (on the Other tab of the Properties box).

Access gets confused if the control has the same name as a field, but is
bound to something else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am printing a report from a table which includes the fields Account Type

(which can be 1 or 2) and Actual Period (which can be positive or negative).
If the account type is 2 I want to print Actual Period with the opposite
sign.

I have created a calculated field in the detail section with a control source as follows :

iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

When I run the report I get #Error in the calculated field.

[Actual Period] and [Account Type] are both bound controls in the detail

section, although they are both invisible.

 

[Guest]

The unbound control (with the Iif control source) has the name Text86.
The bound controls for Account Type and Actual Period (which I have made not visible) both have names which are the same as the control source (as dragged from the field list).

I feel sure the problem is as you say but could you please clarify which control I need to change.

Thanks in anticipation

Change the Name of the control (on the Other tab of the Properties box).

Access gets confused if the control has the same name as a field, but is
bound to something else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am printing a report from a table which includes the fields Account Type

(which can be 1 or 2) and Actual Period (which can be positive or negative).
If the account type is 2 I want to print Actual Period with the opposite
sign.

I have created a calculated field in the detail section with a control source as follows :

iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

When I run the report I get #Error in the calculated field.

[Actual Period] and [Account Type] are both bound controls in the detail

section, although they are both invisible.

Can anyone think what I could be doing wrong ?

 

[Allen Browne]

Okay, so the name Text86 is not the same as a field.

Did you include the equal sign at the start of the ControlSource expression,
i.e.:
=iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The unbound control (with the Iif control source) has the name Text86.
The bound controls for Account Type and Actual Period (which I have made

not visible) both have names which are the same as the control source (as
dragged from the field list).

I feel sure the problem is as you say but could you please clarify which control I need to change.

Thanks in anticipation

Change the Name of the control (on the Other tab of the Properties box).

Access gets confused if the control has the same name as a field, but is
bound to something else.

I am printing a report from a table which includes the fields Account

Type
(which can be 1 or 2) and Actual Period (which can be positive or negative).
If the account type is 2 I want to print Actual Period with the opposite
sign.

I have created a calculated field in the detail section with a control source as follows :

iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

When I run the report I get #Error in the calculated field.

[Actual Period] and [Account Type] are both bound controls in the

detail
section, although they are both invisible.

Can anyone think what I could be doing wrong ?

 

[Guest]

Allen

Your suggestion helped me to find the culprit. [Account Type] was the name of a control which yielded a text result not a number.

Thanks for your help. I thought I was going to be looking at that expression for the rest of my life !

Bob

Change the Name of the control (on the Other tab of the Properties box).

Access gets confused if the control has the same name as a field, but is
bound to something else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am printing a report from a table which includes the fields Account Type

(which can be 1 or 2) and Actual Period (which can be positive or negative).
If the account type is 2 I want to print Actual Period with the opposite
sign.

I have created a calculated field in the detail section with a control source as follows :

iif([Account Type]=1,[Actual Period],[Actual Period]*-1)

When I run the report I get #Error in the calculated field.

[Actual Period] and [Account Type] are both bound controls in the detail

section, although they are both invisible.

Can anyone think what I could be doing wrong ?