Translate option group values to text in a report

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-08-21
I have a report that is built based on a query
The report format is mandated by a higher headquarters and must contain specific data.

My db has a field called Report_Type which has 3 options, 1, 2 , and 3
those types need to be translated on the report to a check box:
Initial (1)
Follow-up (2)
Final (3)

How can I translate or convert a single character text field to the coresponding check box?
Question by:Joe
LVL 75
ID: 38313940
Create this Expression in your query:

RptType: Choose([Report_Type],"Initial","Follow-Up","Final")

LVL 75
ID: 38313947
Opps ... so, you mean 3 check boxes on the Report ?
Not quite following ..
LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 38314280
If you're talking about having 3 checkboxes in your report, then you'd add these  to the Control Source property of each checkbox:

= ([Report_Type]=1)

= ([Report_Type]=2)

= ([Report_Type]=3)

If this is not what you meant, then just like mx, I don't quite follow either.  Please explain a bit more.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 75
ID: 38314285
Since Option Groups are generally mutually exclusive ..... ?

LVL 29

Expert Comment

ID: 38314325
You're right mx, I was thinking of 3 separate checkboxes.  Using a bound option group would make the most sense.
LVL 75
ID: 38314337
Well ... that's the confusion ... actually. Maybe OP really has 3 check boxes in a 'group' ... not clear.

LVL 31

Expert Comment

ID: 38314342
Try this: using a form, modify if otherwise.

"My db has a field called Report_Type which has 3 options, 1, 2 , and 3"
Change optControl with Report_Type, and (replace with your text) as required

Option Group: optControl, its label: optControlLabel, caption: opt Control Caption

opt1, option value 1,       label: opt1Label, caption: opt 1 caption
opt1, option value 2        label: opt2Label, caption: opt 2 caption
opt1, option value 3,       label: opt3Label, caption: opt 3 caption

Private Sub Command7_Click() 
    Dim result As String
    result = Switch(optControl = optControl.Controls(1).OptionValue, optControl.Controls(2).Caption _
                , optControl = optControl.Controls(3).OptionValue, optControl.Controls(4).Caption _
                , optControl = optControl.Controls(5).OptionValue, optControl.Controls(6).Caption)
    Debug.Print result & "(" & optControl & ")"
End Sub

' one result opt 3 Label(3)

Open in new window

To try it with your existing option group, you need to change names of option group name, and options names only. The code will display the relevant existing caption and the option value.

Author Closing Comment

ID: 38316406
Worked like a charm. Thank You!

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question