Can anyone help?



I am writing a report and I need to display a value as something different 

to the actual value. 



It?s a value from an option group.  On the form the options are Yes, No, 

Consider and Don?t Know.  These are represented on the table by ?1, 0, 1 

and 2.  The report is displaying the table values. 



How do I convert the table values to display Yes, No etc.



Thanks



Michelle

This is a job for VBA.  Try this:



' Untested code...

Public Function OptValText(lngStoredVal as Long) as String

	Select Case lngStoredVal

		Case -1

			OptValText = "Yes"

		Case 0

			OptValText = "No"

		Case 1

			OptValText = "Consider"

		Case 2

			OptValText = "Don't Know"

		Case Else

			OptValText = "VALUE OUT OF RANGE!"

	End Select

End Function



In the report, change the control source of your item from the field name to

=OptTextVal([MyField])



HTH,



-Roy



-----Original Message-----

From: Michelle Madden [mailto:michellem@a...]

Sent: Tuesday, April 24, 2001 2:19 PM

To: Access

Subject: [access] Displaying option group values in reports





Can anyone help?



I am writing a report and I need to display a value as something different 

to the actual value. 



It's a value from an option group.  On the form the options are Yes, No, 

Consider and Don't Know.  These are represented on the table by -1, 0, 1 

and 2.  The report is displaying the table values. 



How do I convert the table values to display Yes, No etc.



Thanks



Michelle

Roy & Derrick



Thanks very much for such a quick reply, I'm going to give it a try now.



Thanks again



Michelle





-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: 24 April 2001 21:36

To: Access

Subject: [access] RE: Displaying option group values in reports





This is a job for VBA.  Try this:



' Untested code...

Public Function OptValText(lngStoredVal as Long) as String

	Select Case lngStoredVal

		Case -1

			OptValText = "Yes"

		Case 0

			OptValText = "No"

		Case 1

			OptValText = "Consider"

		Case 2

			OptValText = "Don't Know"

		Case Else

			OptValText = "VALUE OUT OF RANGE!"

	End Select

End Function



In the report, change the control source of your item from the field name to

=OptTextVal([MyField])



HTH,



-Roy



-----Original Message-----

From: Michelle Madden [mailto:michellem@a...]

Sent: Tuesday, April 24, 2001 2:19 PM

To: Access

Subject: [access] Displaying option group values in reports





Can anyone help?



I am writing a report and I need to display a value as something different 

to the actual value. 



It's a value from an option group.  On the form the options are Yes, No, 

Consider and Don't Know.  These are represented on the table by -1, 0, 1 

and 2.  The report is displaying the table values. 



How do I convert the table values to display Yes, No etc.



Thanks



Michelle

You can use various methods such as a simple iif or case statement.



To use IIF statement:

1)  Create a new unbound text box on your report.

2)  Select the new unbound text box

3)  Click Data in Properties form

4)  Type and save in Control Source

      =3DIif([optYourOptionGroupName]) =3D -1, "Yes", iif([optYourOptionGro

upName]) =3D 0 , "No", Iif([optYourOptionGroupName]) =3D 1, "Consider", 

Iif(me! [optYourOptionGroupName]) =3D 2, "Don't Know, "Invalid Option 

Detected"))))



NOTE: optYourOptionGroupName refers to option group that hold -1 , 0, 1 

and 2 values in the report query.



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D



To use a much faster Case Statement method

1)  Create and name (ie. optResult) a new unbound text box on your report.

2)  Click empty Detail part of your report

3)  Click Event in Properties form

4)  Click On Format Event box

5)  Click Builder button (three dots beside down arrow)

6)  Click Code Builder

7)  Click OK

8)  Type and save in this procedure 



     Select case Me!optYourOptionGroupName  (what ever you named your 

option group in the report query)

                Case -1

                           Me!optResult) =3D "Yes"

                Case 0

                           Me!optResult) =3D "No"

               Case 1

                           Me!optResult) =3D "Consider"

               Case 2

                           Me!optResult) =3D "Don't Know"

               Case Else

                          Me!optResult) =3D "Invalid Option Detected"

     End Select



Hope that helps.



_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

________________________________________



>>> michellem@a... 25/04/2001 7:19:32 >>>

Can anyone help?



I am writing a report and I need to display a value as something 

different

to the actual value.



It's a value from an option group.  On the form the options are Yes, 

No,

Consider and Don't Know.  These are represented on the table by *1, 0, 

1

and 2.  The report is displaying the table values.



How do I convert the table values to display Yes, No etc.



Thanks



Michelle