Translate option group values to text in a report

Posted on 2012-08-20
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

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Create this Expression in your query:

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

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Opps ... so, you mean 3 check boxes on the Report ?
    Not quite following ..
    LVL 29

    Accepted Solution

    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.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Since Option Groups are generally mutually exclusive ..... ?

    LVL 29

    Expert Comment

    You're right mx, I was thinking of 3 separate checkboxes.  Using a bound option group would make the most sense.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Well ... that's the confusion ... actually. Maybe OP really has 3 check boxes in a 'group' ... not clear.

    LVL 30

    Expert Comment

    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

    Worked like a charm. Thank You!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now