Convert Option Group Result to Text Value

[Guest]

I am using Access 97 and am writing a Report that displays the data from a
Field called Mollart Division. The data is generated using an Option Group in
a Form using the 3 labels: Sub-contract, Machine Tools and Tooling.

In my Table the Option Group returns the value 1, 2 or 3. However, I want to
display the Label Name rather than the numerical value in the report.

Looking at other postings is seems an iif statement might be the best way to
do this and I have constructed this one: -

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart Division]=2,"Machine
Tools",IIf([Mollart Division]=3,"Tooling")))

where 'Mollart Division' is the field name in the table.

I am VERY confused as to where I would put this statement. Do I put it into
in the properties relating to the field in the Table, the Form or the report?
And more importantly where in the properties?

Thanks for any help in advance!

 

[Wayne Morgan]

Use your statement as the Control Source for the textbox on the report where
you want to display the value of [Mollart Division]. The statement would be
used instead of the Control Source being set to a field.

Just a quick note, if Null isn't a possible answer (i.e. the value will
always be 1, 2, or 3), you can skip the last IIf because it will only return
Tooling if the value isn't 1 or 2.

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart Division]=2,"Machine
Tools","Tooling"))

 

[Guest]

Thanks Wayne, I have tried this but when I run the report it dispays '#Error'
in the text box where the value should be. Any ideas?

TP

Use your statement as the Control Source for the textbox on the report where
you want to display the value of [Mollart Division]. The statement would be
used instead of the Control Source being set to a field.

Just a quick note, if Null isn't a possible answer (i.e. the value will
always be 1, 2, or 3), you can skip the last IIf because it will only return
Tooling if the value isn't 1 or 2.

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart Division]=2,"Machine
Tools","Tooling"))


--
Wayne Morgan
MS Access MVP

I am using Access 97 and am writing a Report that displays the data from a
Field called Mollart Division. The data is generated using an Option Group
in
a Form using the 3 labels: Sub-contract, Machine Tools and Tooling.

In my Table the Option Group returns the value 1, 2 or 3. However, I want
to
display the Label Name rather than the numerical value in the report.

Looking at other postings is seems an iif statement might be the best way
to
do this and I have constructed this one: -

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart Division]=2,"Machine
Tools",IIf([Mollart Division]=3,"Tooling")))

where 'Mollart Division' is the field name in the table.

I am VERY confused as to where I would put this statement. Do I put it
into
in the properties relating to the field in the Table, the Form or the
report?
And more importantly where in the properties?

Thanks for any help in advance!

 

[Wayne Morgan]

What is the name of the textbox? Is it also [Mollart Division]? If so, try
changing the name of the textbox to txtMollartDivision. Where is the textbox
located (which section)? Is the field available in the report's query? If it
still doesn't work, you may need to put a hidden textbox in the report
section and bind it to the field, then refer to that textbox instead of the
field. Again, no duplicate names.

--
Wayne Morgan
MS Access MVP

Thanks Wayne, I have tried this but when I run the report it dispays
'#Error'
in the text box where the value should be. Any ideas?

TP

Use your statement as the Control Source for the textbox on the report
where
you want to display the value of [Mollart Division]. The statement would
be
used instead of the Control Source being set to a field.

Just a quick note, if Null isn't a possible answer (i.e. the value will
always be 1, 2, or 3), you can skip the last IIf because it will only
return
Tooling if the value isn't 1 or 2.

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart
Division]=2,"Machine
Tools","Tooling"))


--
Wayne Morgan
MS Access MVP

I am using Access 97 and am writing a Report that displays the data from
a
Field called Mollart Division. The data is generated using an Option
Group
in
a Form using the 3 labels: Sub-contract, Machine Tools and Tooling.

In my Table the Option Group returns the value 1, 2 or 3. However, I
want
to
display the Label Name rather than the numerical value in the report.

Looking at other postings is seems an iif statement might be the best
way
to
do this and I have constructed this one: -

=IIf([Mollart Division]=1,"Sub-contract",IIf([Mollart
Division]=2,"Machine
Tools",IIf([Mollart Division]=3,"Tooling")))

where 'Mollart Division' is the field name in the table.

I am VERY confused as to where I would put this statement. Do I put it
into
in the properties relating to the field in the Table, the Form or the
report?
And more importantly where in the properties?

Thanks for any help in advance!

 

[Guest]

You would put the IIF statement in the control source of the text box in the
form or report.

I would advice you to create a new table that contain the two fields
Mollart_Division_code
Mollart_Division_Desc

and then use dlookup instead of the iif
=dlookup("Mollart_Division_Desc","TableName","Mollart_Division_code =" &
[Mollart Division])

That way if you create another Mollart Division code,you just need to enter
it in the table, and you dont need to remember in which fields in which forms
or reports in the database you created this iif statement.
It will save alot in maintnance time and errors, especialy if new developers
will start working with the system.