iff statement on form control

[Jo-Anne]

I'm creating a report that will give total numbers of Males and Femals. The
data in the table comes from a check box so the values are 1 & 2. When I
display the data on the report I would like to display 'Male' or 'Female' not
1 or 2. The source of the report is a query which has the field memSex. I
thought this code would work but I get an #error message in my control when I
run the report.

=IIf([memSex]=1,"Male","Female")

Any help would be great.

 

[Ofer]

Is the memSex field name come from the table? (It should)
Is the memSex Field type is number? If it string then try

=IIf([memSex]="1","Male","Female")

A check box gives True and False values (-1/0), How did you get to 1 and 2?
If it True and Flase try

=IIf([memSex]=True,"Male","Female")

 

[Jeff Boyce]

Jo-Anne

If you are using a table with a Yes/No field (?a checkbox?), the values are
actually 0 for false/no, and non-zero for true/yes (Access uses -1,
SQL-Server uses 1, ...?).

What is the text of your error message?

Have you bound the control in your report to the Yes/No field? If so, you
are trying to set a text value (Male, Female) in a boolean control. You
might need to create a new textbox, leave it unbound to any field, and use
your IIF() statement there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Jo-Anne]

Hi Jeff,

Sorry for the delayed reply.

I am using a checkbox but the values in my table (and when show on the form)
are 1 for Male and 2 for Female.

I don't actually get an error message whe I run the report the data in the
text box with the calcualtion in it shows #error. On the design view of the
form I have error checking on and it shows 'Invalid Contorl Property: Control
Source' and 'Circular Referencing'. Although these errors appear on another
calcualted control that works fine when the report runs.

The text box is not bound to anything. The control source is the IIF
statement.

I'm still at a loss as to why this is not working so anymore suggestions
would be great.

Thanks

Jo-Anne

If you are using a table with a Yes/No field (?a checkbox?), the values are
actually 0 for false/no, and non-zero for true/yes (Access uses -1,
SQL-Server uses 1, ...?).

What is the text of your error message?

Have you bound the control in your report to the Yes/No field? If so, you
are trying to set a text value (Male, Female) in a boolean control. You
might need to create a new textbox, leave it unbound to any field, and use
your IIF() statement there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I'm creating a report that will give total numbers of Males and Femals. The
data in the table comes from a check box so the values are 1 & 2. When I
display the data on the report I would like to display 'Male' or 'Female' not
1 or 2. The source of the report is a query which has the field memSex. I
thought this code would work but I get an #error message in my control when I
run the report.

=IIf([memSex]=1,"Male","Female")

Any help would be great.

 

[Jeff Boyce]

Jo-Anne

That sounds like an option group, with the first choice = 1 and the second
choice = 2. Perhaps you & I don't mean the same thing by "checkbox"?

You mention "design view of the form" and "run[ning] the report". I can't
tell from your description whether you are working on a form or a report
(forms in Access display data and provide a data entry mechanism; reports
display data in a printed format and don't allow data entry).

I don't understand the connection between the errors you reported and the
other "calculated control".

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Hi Jeff,

Sorry for the delayed reply.

I am using a checkbox but the values in my table (and when show on the form)
are 1 for Male and 2 for Female.

I don't actually get an error message whe I run the report the data in the
text box with the calcualtion in it shows #error. On the design view of the
form I have error checking on and it shows 'Invalid Contorl Property: Control
Source' and 'Circular Referencing'. Although these errors appear on another
calcualted control that works fine when the report runs.

The text box is not bound to anything. The control source is the IIF
statement.

I'm still at a loss as to why this is not working so anymore suggestions
would be great.

Thanks

Jo-Anne

If you are using a table with a Yes/No field (?a checkbox?), the values are
actually 0 for false/no, and non-zero for true/yes (Access uses -1,
SQL-Server uses 1, ...?).

What is the text of your error message?

Have you bound the control in your report to the Yes/No field? If so, you
are trying to set a text value (Male, Female) in a boolean control. You
might need to create a new textbox, leave it unbound to any field, and use
your IIF() statement there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I'm creating a report that will give total numbers of Males and

Femals.
The

data in the table comes from a check box so the values are 1 & 2. When I
display the data on the report I would like to display 'Male' or

'Female'
not

1 or 2. The source of the report is a query which has the field

memSex.
I

thought this code would work but I get an #error message in my control when I
run the report.

=IIf([memSex]=1,"Male","Female")

Any help would be great.

 

[Jo-Anne]

Hi Jeff,

Please let me clarify. I am using an option group on a form to gather the
data. The problem I am having is on a report. And finally I'm also not sure
what the connection is between the errors. I actually don't think the error
have anything to do with the problem - but I thought I would let you be the
judge of that.

Now I believe there is some sort of syntax or data type problem because if I
omit the =1 in the iif statement ([iif(memID], "Male", "Female") I don't get
the error message, althought the answer will always be 'Male'.

Sorry for not being clear, I hope this helps you understand my problem!

Thanks.

Jo-Anne

That sounds like an option group, with the first choice = 1 and the second
choice = 2. Perhaps you & I don't mean the same thing by "checkbox"?

You mention "design view of the form" and "run[ning] the report". I can't
tell from your description whether you are working on a form or a report
(forms in Access display data and provide a data entry mechanism; reports
display data in a printed format and don't allow data entry).

I don't understand the connection between the errors you reported and the
other "calculated control".

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Hi Jeff,

Sorry for the delayed reply.

I am using a checkbox but the values in my table (and when show on the form)
are 1 for Male and 2 for Female.

I don't actually get an error message whe I run the report the data in the
text box with the calcualtion in it shows #error. On the design view of the
form I have error checking on and it shows 'Invalid Contorl Property: Control
Source' and 'Circular Referencing'. Although these errors appear on another
calcualted control that works fine when the report runs.

The text box is not bound to anything. The control source is the IIF
statement.

I'm still at a loss as to why this is not working so anymore suggestions
would be great.

Thanks

Jo-Anne

If you are using a table with a Yes/No field (?a checkbox?), the values are
actually 0 for false/no, and non-zero for true/yes (Access uses -1,
SQL-Server uses 1, ...?).

What is the text of your error message?

Have you bound the control in your report to the Yes/No field? If so, you
are trying to set a text value (Male, Female) in a boolean control. You
might need to create a new textbox, leave it unbound to any field, and use
your IIF() statement there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I'm creating a report that will give total numbers of Males and Femals.
The
data in the table comes from a check box so the values are 1 & 2. When I
display the data on the report I would like to display 'Male' or 'Female'
not
1 or 2. The source of the report is a query which has the field memSex.
I
thought this code would work but I get an #error message in my control
when I
run the report.

=IIf([memSex]=1,"Male","Female")

Any help would be great.

 

[Jeff Boyce]

Jo-Anne

Give this a try, sometimes some versions of Access got confused if you
created a textbox in the report that was bound, then turned it into a
calculated control...

Open the report in design mode. Create a new (unbound) textbox control.

In the control's ControlSource property, enter:

=IIF([memSex]=1,"Male","Female")

Note: this requires that the query underlying your report has a field named
"memSex".

Note2: can you confirm that the query's [memSex] field is never Null?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Hi Jeff,

Please let me clarify. I am using an option group on a form to gather the
data. The problem I am having is on a report. And finally I'm also not sure
what the connection is between the errors. I actually don't think the error
have anything to do with the problem - but I thought I would let you be the
judge of that.

Now I believe there is some sort of syntax or data type problem because if I
omit the =1 in the iif statement ([iif(memID], "Male", "Female") I don't get
the error message, althought the answer will always be 'Male'.

Sorry for not being clear, I hope this helps you understand my problem!

Thanks.

Jo-Anne

That sounds like an option group, with the first choice = 1 and the second
choice = 2. Perhaps you & I don't mean the same thing by "checkbox"?

You mention "design view of the form" and "run[ning] the report". I can't
tell from your description whether you are working on a form or a report
(forms in Access display data and provide a data entry mechanism; reports
display data in a printed format and don't allow data entry).

I don't understand the connection between the errors you reported and the
other "calculated control".

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Hi Jeff,

Sorry for the delayed reply.

I am using a checkbox but the values in my table (and when show on the form)
are 1 for Male and 2 for Female.

I don't actually get an error message whe I run the report the data in the
text box with the calcualtion in it shows #error. On the design view

of
the

form I have error checking on and it shows 'Invalid Contorl Property: Control
Source' and 'Circular Referencing'. Although these errors appear on another
calcualted control that works fine when the report runs.

The text box is not bound to anything. The control source is the IIF
statement.

I'm still at a loss as to why this is not working so anymore suggestions
would be great.

Thanks

:

Jo-Anne

If you are using a table with a Yes/No field (?a checkbox?), the

values
are

actually 0 for false/no, and non-zero for true/yes (Access uses -1,
SQL-Server uses 1, ...?).

What is the text of your error message?

Have you bound the control in your report to the Yes/No field? If

so,
you

are trying to set a text value (Male, Female) in a boolean control. You
might need to create a new textbox, leave it unbound to any field,

and
use

your IIF() statement there.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I'm creating a report that will give total numbers of Males and Femals.
The
data in the table comes from a check box so the values are 1 & 2. When I
display the data on the report I would like to display 'Male' or 'Female'
not
1 or 2. The source of the report is a query which has the field memSex.
I
thought this code would work but I get an #error message in my control
when I
run the report.

=IIf([memSex]=1,"Male","Female")

Any help would be great.