CODEWITHSUNDEEP
ms-accessvba
user1680893
user1680893

Reputation: 31

How do I change the values of a radio button from 1, 2, 3, etc to text values?

I have created a small database in Access 2007 that consists of one table and two forms, one for entering data, and one for retrieving data.

My problem is this: On my input form I have a group box with three radio buttons in it. The question being asked is Is the element a sensor?
The buttons represent Yes, No, and Don’t Know.

In the database I have a column named Sensor to hold the value the user chose, but since the radio buttons return a value of 1 for yes, 2 for no, or 3 for don't know, it makes generating a report or query that makes sense to the user very difficult.

At this point I’m writing huge SQL statements with nested iif’s to return the data the way I want to see it.

Is there a way to populate the table with data the way I want to see it (yes, no, don’t know) instead of populating it with 1’s 2’s or 3’s? This is a bound form by the way, I wish I would have done it unbound, but I can’t go back now.

Upvotes: 3

Views: 8188

Answers (3)

HansUp
HansUp

Reputation: 97131

"since the radio buttons return a value of 1 for yes, 2 for no, or 3 for don't know, it makes generating a report or query that makes sense to the user very difficult."

Store those 3 pairs as rows in a Sensor_Values table:

sval descriptor
1    yes
2    no
3    don't know

Then you can join that table to the table which includes the stored Sensor numbers.

SELECT yt.Sensor, sv.descriptor
FROM
    YourTable AS yt
    INNER JOIN Sensor_Values AS sv
    ON yt.Sensor = sv.sval;

If you're opposed to creating and joining a lookup table, you could use a Switch() expression in your queries to translate the numeric Sensor values to their text forms.

SELECT
    Switch(
        Sensor = 1, "yes",
        Sensor = 2, "no",
        Sensor = 3, "don't know"
    ) AS sensor_text
FROM YourTable;

The Switch() approach can work, but can be more challenging to maintain compared to the lookup table approach.

My intention here was to show you fairly simple methods to use the option group value as a number instead of "populate the table with data the way I want to see it (yes, no, don’t know) instead of populating it with 1’s 2’s or 3’s"

As a general rule, you will be better off working with Access controls as they were designed to be used. Break that rule whenever you have a compelling reason ... but breaking the rule then requires additional efforts from you ... like more VBA code. The approaches I suggested don't require any VBA.

Upvotes: 2

SeanC
SeanC

Reputation: 15923

you could use a Select Case when creating the select string, instead of having iif's nested in the SQL.

Select Case Me.rdoSensor
    Case 1
        sSQL=sSQL & " AND Sensor='Yes'"
    Case 2
        sSQL=sSQL & " AND Sensor='No'"
    Case 3
        sSQL=sSQL & " AND Sensor='Don''t know'"
End Select

Upvotes: 1

dmarra
dmarra

Reputation: 863

I would suggest not using the radio buttons, and instead opt for a combo box. You'll be able to use string values for the results directly in the combo box.

Now if you are dead set on using radio buttons, try this:

Add a new field to your table that holds text. Bind this to a hidden text box on your form. Then, add a BeforeUpdate event (or AfterUpdate depending on what you are doing) to the radio group. Add code similar to the following:

Sub RadioGroup_BeforeUpdate(cancel As Integer)
    Select Case Me.RadioGroup.Value
        case 1
            Me.hiddenTextField.value = "Yes"
        case 2
            Me.hiddenTextField.value = "No"
        case else
            Me.hiddenTextField.value = "Don't Know"
    End Select
End Sub

Now when you save the record, the human readable value will be available in the new field you added.

Upvotes: 2

Related Questions